Tag Archives: T-SQL

Finding the Fullness of all Database files

Posted on by
Reply

One of the common things a Database Admin would need to monitor is the size of the database files and along with that monitor the level to which those files are full, so as to plan for the future growth of the database or to be proactive in allocating additional data files if the existing one are full.

Such a task is fine with a few databases at a time, but if there are lot of databases on a server, it is difficult to do such a task. Writing T-SQL scripts for doing such a task a solution to overcome repetitive tasks. The following script enables the user to list the database files which are full to the desired level.

To change the format so that you get the list of all database files, change the @PCTFULL variable value to 0. This script assumes that the user requires the list of the database files which are nearly full (80 % or more)

For the convenience of users, the script can be copied from the text at the end of the post.

You can find the code here..

DECLARE @PCTFULL INT

SET @PCTFULL = 0

DECLARE @sql VARCHAR(4000), @dbName varchar(255),
@LFNAME varchar(255), @PFNAME varchar(255),
@PCTUSD varchar(255)

CREATE TABLE #MainTemp (DBNAME varchar(128),LFName Varchar(500),
PhysLoc Varchar(500), TotalExts DECIMAL(10,2),UsedExtents
DECIMAL(10,2))

CREATE TABLE #Temp1 (FileID INT, FileGroup INT, TotalExts INT,
UsedExtents INT, LFName Varchar(500) , PhysLoc Varchar(500))

DECLARE  DataBaseNamesCursor CURSOR FOR
SELECT  [name] FROM master.sys.databases

OPEN DataBaseNamesCursor

FETCH NEXT FROM DataBaseNamesCursor INTO @dbName
WHILE @@fetch_status=0

BEGIN

SET @sql = ' USE ' + @dbname + CHAR(13)
+ 'Insert into #Temp1 (FileID,FileGroup,TotalExts,UsedExtents,
LFName,PhysLoc )' + CHAR(13) +  'EXEC (''DBCC showfilestats
with no_infomsgs'')' + CHAR(13) + 'Insert into #MainTemp'
+ CHAR(13)
+  'Select ''' + @dbname + ''', Temp.LFName,Temp.PhysLoc,
Temp.TotalExts,Temp.UsedExtents from #Temp1 Temp INNER JOIN '
+ @dbname + '.dbo.sysfiles SF ON Temp.FileID = SF.FileID
AND Temp.LFName = SF.Name AND Temp.PhysLoc = SF.FILEName '

EXEC(@sql)

FETCH next FROM DataBaseNamesCursor INTO @dbName

END

CLOSE DataBaseNamesCursor
DEALLOCATE DataBaseNamesCursor

SELECT  DBNAME, LFName , PhysLoc,
CEILING((UsedExtents / TotalExts) * 100)
AS PERCENTAGE_USED FROM #MainTemp WHERE
CEILING((UsedExtents / TotalExts) * 100) > @PCTFULL
ORDER BY PERCENTAGE_USED DESC

DROP TABLE #Temp1

DROP TABLE #MainTemp

— Bru Medishetty

Renaming database and database objects using T-SQL

Posted on by
Reply

With increased usage of SQL Server Management Studio for carrying out most of the day to day tasks, performing many activities is easier to learn and to master those steps from the Management Studio.

This is not always a possibility, assume a task needs to be done at certain specific time or after an event / step / process or it may be required to perform something repeatedly for a number of objects, it would be highly diffucult to repeat that task doing it from the Management Studio.

To rename a database, use the system stored procedure SP_RENAMEDB.

Also in the same way, to rename a database object, use the SP_RENAME system stored procedure.

 

Both the stored procedures accept 2 parameters out of which, the first parameter is the current name of the Database / Object and the second parameter is the new name to which it needs to be renamed to. Also find that the values are string values and hence, enclosed in single quotes.

– Bru Medishetty

Concatenating Strings to NULL values

Posted on by
Reply

We recently ran into this situation of reports containing nothing but blank spaces. When investigated the reason was that the String concatenation statement was written as a general statement. There is nothing wrong in the statement, it works fine when the variables have a value but does not work when one of the variables is NULL.

The “SELECT @FinalString” statement returns NULL since the variable @String1 is NULL. When such a variable is concatenated with other set of variables or string values it would all result in NULL and the string message(s) that is to be used will not return the desired output (resulting in blank spaces in reports in one of our case).

In order to overcome such issues, the ISNULL function can be used which checks the value and replaces with the value to be replaced if NULL is found. The same statement is rewritten and ISNULL is implemented to check for NULL values in @String1 variable, if @String1 is NULL, then ‘EMPTY STRING1’ is included in the final string and results in a meaningful string value instead of NULL.

Note that the appropriate statement would also include ISNULL(@String2,’EMPTY STRING2′), it has been avoided in the example to emphasize its importance at the @String1..

For more information on ISNULL look at Books Online link here

— Bru Medishetty