Tag Archives: Monitoring

Find Transaction Log Space Used

Posted on by
Reply

To query the space used by the transaction logs use the command, use DBCC SQLPERF(LOGSPACE). The command displays the Database Name, Log Size in Mega Bytes, the % of  log file used, remember the value is not the size it is the percentage used from the actual space of the log files. The results displayed is for all databases on the SQL Server instance.

Find the script below…

DBCC SQLPERF(LOGSPACE)

Monitoring transaction log files for the free space is a one of the very important tasks for a DBA.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Backup Information for a Database

Posted on by
Reply

How do I find the backup information about a particular database ?

Some one asked me this question in the past and since it is being repeatedly asked in forums and to me personally , I decided to write this.

Having the backup information of a database is very important for a database administrator. In case of disaster, the DBA has to be aware when was the last time that database was backed up.  If a SQL Server is inherited from another Admin and the schedule of the backups and the backup types performed is not known it is easy to find by querying the backup related tables in MSDB.

One of those tables is msdb.dbo.backupset. It has the backup information related to all databases on that instance. information such as backup size, backup type and the start and end time of the backup to name a few.

The following script is one such script that gives the backup info of a specific database with the most recent one first.

The value in bkup_type column indicates the type of backup performed. D indicates FULL backup, I indicates Differential and L indicates Log backup.

Use the following Script.

DECLARE @DBNAME VARCHAR(50)

SET @DBNAME = 'LSWB'

SELECT CEILING(((backup_size / 1024)/1024)/1024)
Bkup_Size_In_GB,
CEILING(((backup_size / 1024)/1024)) Bkup_Size_In_MB,
[type] Bkup_Type,
backup_start_date StartTime,backup_finish_date
FROM msdb.dbo.backupset
WHERE DATABASE_NAME = @DBNAME
ORDER BY backup_start_date DESC

— Bru Medishetty

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