0

We are running exec xp_fixeddrives to get the free space for each physical drive associated with the SQL Server.

I am running this code as part of an SSIS package which fetches all the SQL servers free space.

Currently I am creating a table in tempdb and inserting the results of exec xp_fixeddrives into this table. But the issue I am facing is, when ever the server is restarted I am facing access issue as the table is on Tempdb.

I don't really like the idea of creating a table in Master DB or Model DB. A challenge I am facing is we execute on difference instance of SQL Server versions ranging from 2000 - 2014. So obviously there are issues I have to keep in mind.

Any suggestion on this are much appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Prav54
  • 3
  • 4
  • why don't you use #temp table ? – Squirrel Feb 29 '16 at 05:19
  • 2
    Create your own, separate database schema for this and create a normal, fixed table inside that database schema ... – marc_s Feb 29 '16 at 05:34
  • Add `IF NOT EXISTS` code beforehand to create the table in tempdb if it doesn't exist. – Nick.Mc Feb 29 '16 at 06:03
  • 1
    This logic clearly appears to be part of your application. I would create a table in a database that you can access from your code at all times. This is the typical way to store information that your application needs. Do not use the model db as this is a template. Avoid changes to the master. It is better to be explicit in your intent anyway. – cameront Feb 29 '16 at 06:20
  • The logic is more a workaround to get data out of a stored procedure.... which I guess is part of the application – Nick.Mc Mar 01 '16 at 02:49

1 Answers1

1

That's obvious, SQL Server reset TempDb whenever SQL Services is restarted. In that case, you will face access issues because that table won't exists. I would probably create my own table to store the details if I want to store historical check information also.

If you are running your code from SSIS and you want to send mail just after validating it then you don't even have to create any table. Just fill a object variable in SSIS from execute SQL task which will be running below query

DECLARE @t TABLE 
(Drive VARCHAR(1),Size INT)

INSERT INTO @t 
EXEC MASTER..xp_fixeddrives

SELECT * FROM @t

Read this object variable in script task to send mail.

Anuj Tripathi
  • 2,251
  • 14
  • 18
  • I am running drivespace to extract space details for about 15 servers and most likely will be adding more servers in the future. I have this in Foreach loop container. Is there a way that I can get all these store into a variable. Currently I am inserting each record of the server into the table on tempdb so that way after extracting the report I drop the table. – Prav54 Mar 01 '16 at 02:16
  • That's a convoluted way to get drivespace. Have you tried using WMI istead? – Nick.Mc Mar 01 '16 at 02:49