So, in the previous post we talked about changing the number of SQL Error logs. With that done, we now need to actually make use of all these logs. I like keeping each days errors in its own log file. Then if a problem arises and it was last week Tuesday, well then look in the corresponding log file.
There is a system stored procedure sp_cycle_errorlog, that will do exactly the above, close one log and open a new log. All I do now is schedule this stored procedure as a SQL Agent Job to run at midnight every day. Thats easy enough on 1 server, but remember, I have 151. So I script out the SQL Agent Job. Great, now I have a .sql file that I need to deploy to 151 servers. Thats if I'm not using a master - target server configuration. That in itself is a fantastic concept, but I've never seen it at any client I've started at.
Another blogger, SerialSeb, posted a very handy piece of code that does what I need to do. Check out his post.
Basically what he does is create a batch file that contains a sqlcmd command. This command as you know can execute the contents of a file against a said SQL server. Then the code runs the batch file using cmd /c. Go to the command prompt and type: cmd /?. It returns "/C Carries out the command specified by string and then terminates". If the batch file exists the contents of it are overwritten. Good one Seb.
Great, so unit of work encapsulted, got list of servers, its go time.
foreach ($server in $serverlist) {Execute-SqlFile $file $server $dbname $WindowsAuthentication=true}
Where $file is the .sql file containing the T-SQL for the SQL Agent job. $dbname doesn't matter because the .sql file changes the database to MSDB anyway.
Go forth and deploy.
Monday, April 20, 2009
Change the number of SQL Error Logs using Powershell
One of the first places to start troubleshooting a potential SQL problem is to look at the SQL Error Logs. If there is a serious problem, it will normally show up in this log. The thing is, the default setting for SQL is to keep 6 logs. Each log is created when SQL Server starts up. So if a SQL server is restarting often, which is already a problem, you could have very little SQL error log history. On the other hand if SQL stays up for weeks or months at a time, which is a good thing, you will potentially have a very big error log to sift through to find any errors.
I recommend configuring 31 log files. This number can be anything from 6 to 99. I choose 31 because that will give me a months worth of error logs. How so if the error logs only get created when SQL Server restarts? I'll cover that in a subsequent blog.
But first, actually changing the number of error logs. One could obviously do it in Enterprise Manager or SQL Management Studio. But in my case I started at a client with 151 SQL servers of differing versions. Click click, click click. Very tedious. You guessed it, Powershell! :-)
What I like to do is create functions that encapsulate the unit of work that I want to accomplish. Once I have this function I can easily and neatly deploy this unit of work to a list of servers.
The function to change the number of SQL Error Logs is quite straighforward. It uses the Reg command to add the NumErrorLogs value to a certain registry key. There is actually no way to do this through T-SQL, unless I use xp_regwrite extended stored procedure. But that would entail connecting to SQL to write a value to the registry. Not necessary. I actually do connect to SQL, but only to determine the version of SQL. This I guess I could also do in the registry, but in my function I've connected to SQL using SMO.
So lets look at some code. Following is the function code:
The code for get-sqlserver is:
Great, so now I have my unit of work defined as Change-ErrorLogs. You can probably guess the rest. But if you can't, here is the code:
Servers2.txt contains a list of all my SQL Servers. Grab that list into $ss. And then for each server ($s) in the list ($ss), Change the number of error logs (Change-ErrorLogs). Tada!
I recommend configuring 31 log files. This number can be anything from 6 to 99. I choose 31 because that will give me a months worth of error logs. How so if the error logs only get created when SQL Server restarts? I'll cover that in a subsequent blog.
But first, actually changing the number of error logs. One could obviously do it in Enterprise Manager or SQL Management Studio. But in my case I started at a client with 151 SQL servers of differing versions. Click click, click click. Very tedious. You guessed it, Powershell! :-)
What I like to do is create functions that encapsulate the unit of work that I want to accomplish. Once I have this function I can easily and neatly deploy this unit of work to a list of servers.
The function to change the number of SQL Error Logs is quite straighforward. It uses the Reg command to add the NumErrorLogs value to a certain registry key. There is actually no way to do this through T-SQL, unless I use xp_regwrite extended stored procedure. But that would entail connecting to SQL to write a value to the registry. Not necessary. I actually do connect to SQL, but only to determine the version of SQL. This I guess I could also do in the registry, but in my function I've connected to SQL using SMO.
So lets look at some code. Following is the function code:
function Change-ErrorLogs ($s)
{
$svr = get-sqlserver $s
if (($svr.get_information().version.major) -eq 8)
{
#SQL 2000
reg add \\$s\HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer /v NumErrorLogs /t REG_DWORD /d 31
write-host "SQL 2000 Server - $s Config changed"
}
else
{
#SQL 2005
reg add "\\$s\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer" /v NumErrorLogs /t REG_DWORD /d 31
write-host "SQL 2005 Server - $s Config changed"
}
}
The code for get-sqlserver is:
function Get-SqlServer
{
param([string]$sqlserver=$(throw 'Get-SqlServer:`$sqlserver is required.'))
#When $sqlserver passed in from the SMO Name property, brackets
#are automatically inserted which then need to be removed
$sqlserver = $sqlserver -replace "\[|\]"
Write-Verbose "Get-SqlServer $sqlserver"
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlserver
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject")
#trap { "Check $SqlServer Name"; continue} $server.ConnectionContext.Connect()
return $server
} #Get-SqlServer
Great, so now I have my unit of work defined as Change-ErrorLogs. You can probably guess the rest. But if you can't, here is the code:
$ss = gc 'servers2.txt'
foreach ($s in $ss) {Change-ErrorLogs $s}
Servers2.txt contains a list of all my SQL Servers. Grab that list into $ss. And then for each server ($s) in the list ($ss), Change the number of error logs (Change-ErrorLogs). Tada!
Wednesday, April 8, 2009
Resolving a list of IP Addresses to Hostnames using Powershell
Following on from my post on Auditing and Summarizing Logon activity on a SQL Server, I figured I need to automate the resolving of IP Addresses to Host names to make the Audit information more useful.
So off I go to Google or MSDN and find [System.Net.Dns]::GetHostbyAddress($IP). Nice! This function can be called from Powershell and does exactly what I need it to do, resolve an IP Address (string) to a hostname.
I created a Powershell Function that wraps this function and adds some rudimentary error handling. I need error handling (who or what doesn't) because the hosts that populates list of IP addresses may not always be available and the host name resolution will fail. Powershell Resolve-IP function code:
Cool, so now we can grab our list of IP Addresses from our AuditLoginHistory table. I do this by using a SQL SMO function called get-sqldata that runs a T-SQL query and returns the results. So my T-SQL Query is:
select distinct host from auditloginhistory
I then use a foreach loop to iterate through the IP Addresses and resolve each IP address to a hostname. The final results, in my case, is exported to a .csv. I'll then Bulk Insert this csv list of IP Addresses and Host names into a SQL table using another SQL SMO function. Then I'll join the Host_IP table to my AuditLoginHistory and display both IP Address and Hostname.
Below is the Powershell script that runs the above:
So off I go to Google or MSDN and find [System.Net.Dns]::GetHostbyAddress($IP). Nice! This function can be called from Powershell and does exactly what I need it to do, resolve an IP Address (string) to a hostname.
I created a Powershell Function that wraps this function and adds some rudimentary error handling. I need error handling (who or what doesn't) because the hosts that populates list of IP addresses may not always be available and the host name resolution will fail. Powershell Resolve-IP function code:
Function Resolve-IP ($IP) {
trap {
write-host "An error occured: "
write-host "ID: " $_.ErrorID
write-host "Message: "$_.Exception.Message
throw "Couldn't Resolve $IP"
}
$results = [System.Net.Dns]::GetHostbyAddress($IP)
$results
}
Cool, so now we can grab our list of IP Addresses from our AuditLoginHistory table. I do this by using a SQL SMO function called get-sqldata that runs a T-SQL query and returns the results. So my T-SQL Query is:
select distinct host from auditloginhistory
I then use a foreach loop to iterate through the IP Addresses and resolve each IP address to a hostname. The final results, in my case, is exported to a .csv. I'll then Bulk Insert this csv list of IP Addresses and Host names into a SQL table using another SQL SMO function. Then I'll join the Host_IP table to my AuditLoginHistory and display both IP Address and Hostname.
Below is the Powershell script that runs the above:
. ./Function_Resolve-IP.ps1
$hosts = get-sqldata 'sql1' master "select distinct host from auditloginhistory"
foreach ($IP in $hosts) {Resolve-IP $IP.host.tostring()}
$ipex = {foreach ($IP in $hosts) {Resolve-IP $IP.host.tostring()}}
$ipex.invoke() | Select @{N="HostName";E={$_.HostName.tostring()}}, @{N="IP_Address";E={$_.get_AddressList()}} | export-csv "host-ip.csv" -NoTypeInformation
Monday, April 6, 2009
Auditing and Summarizing Logons to SQL Server
I'm sure you've come across a scenario where at a new client or employer you want to embark on a SQL Server Login cleanup. But a lot of the logins are from the dark ages and the current IT staff don't know which logins are used by who and for what.
Well there are two ways to view which logins are logging onto the SQL server and when: The one way is to enable successful and failed logon attempts at the SQL server level. This will write an entry to the SQL Error Log for every failed or successful logon. On a busy server this would make the error log huge. Besides that, the audit information is in a flat text file, which can be viewed, but not summarised easily.
Another way to audit logons to your SQL Server is to enable a server wide Logon Trigger, which as you know is available in SQL 2005 & 2008. See BOL for more details. This trigger exposes some XML Data about the logon and this data is made accessible using the EVENTDATA function.
The solution I've written to audit and summarize logon information uses a logon trigger, two logon Audit tables and a daily summarizing script. I've created these tables in the Master database, but they should ideally be created in a user database. The first table, AuditLogin stores all the data that results from the logon trigger firing. I've chosen to store ServerName, EventTime, ServerLogin, ClientHost, LoginType and IsPooled event data. The trigger basically writes these values to the AuditLogin table for a LOGON event. Code:
This is all good and well, now we know who is logging onto our Server and when and from where. But this trigger firing can fill a large hard disk quickly. On a busy server there may be millions of entries into this Audit table daily. This is mostly as a result of connection pooling and how a connection is kept alive by an application server like IIS. So for the most part I don't worry to much about huge amounts of logons from pooled connections from application servers. The logon entries I'm more interested in are for non pooled connections.
So what we need, from an audit summary point of view is a distinct record for each logon from a distinct host and the logons pooled status, and the last time this logon logged in and how many times it logged in. This will give me some valuable information from the millions of records or raw logon data. Information like which logins have not logged in since Logon Auditing has been enabled, which generic logins are being used from which hosts and which non pooled logins are logging in the most. From my original mission of establishing which logins are used and how often, we can now determine this.
Now I need to take the daily logon audit information and summarize it and merge todays summarized information with the historic logon information. This is a very common scenario in a Data Warehouse environment. I NEED to summarize and TRUNCATE my AuditLogin table after I've summarized the data in it because of the huge amount of data that goes into this table every day. Knowing that login x logged on at a particular time yesterday is of little value. So I don't need the detail data, unless there is a security breach. Summarize, Merge to History, Truncate. Treat it mean, keep it clean.
The below code uses some incremental load logic I've gleened from my Data Warehouse experience. Basically the first full load and subsequent incremental loads of my AuditLoginHistory table use the same code. Thats pretty handy. I can run the same code to load the history table from the first load.
Basically each record in the history table is identified by Login, Host and Pooled. The two other fields are LoginCount and LastLoggedIn. So what the below code does is first summarize todays logon information. Then it compares it to the History information. If the same record exists, the LoginCount and LastLoggedIn is updated. If the record doesn't exist, a new one is inserted into the history table.
Summarizing Code:
Below is the code for the two Audit tables:
Lastly, a caveat to make sure the trigger fires and doesn't cause logon failures: Ensure that all the objects that get created are accessible to public. Otherwise logons will fail because the user logging in causes the trigger to fire and the write to the Audit table is done in the context of the user thats logging on.
Well there are two ways to view which logins are logging onto the SQL server and when: The one way is to enable successful and failed logon attempts at the SQL server level. This will write an entry to the SQL Error Log for every failed or successful logon. On a busy server this would make the error log huge. Besides that, the audit information is in a flat text file, which can be viewed, but not summarised easily.
Another way to audit logons to your SQL Server is to enable a server wide Logon Trigger, which as you know is available in SQL 2005 & 2008. See BOL for more details. This trigger exposes some XML Data about the logon and this data is made accessible using the EVENTDATA function.
The solution I've written to audit and summarize logon information uses a logon trigger, two logon Audit tables and a daily summarizing script. I've created these tables in the Master database, but they should ideally be created in a user database. The first table, AuditLogin stores all the data that results from the logon trigger firing. I've chosen to store ServerName, EventTime, ServerLogin, ClientHost, LoginType and IsPooled event data. The trigger basically writes these values to the AuditLogin table for a LOGON event. Code:
/****** Object: DdlTrigger [trigLogon] Script Date: 04/06/2009 10:57:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trigLogon]
ON ALL SERVER
FOR LOGON
AS
BEGIN
declare @LogonTriggerData xml
SET @LogonTriggerData = eventdata() ;
INSERT INTO AuditLogin
(ServerName, EventTime, ServerLogin, Col_ClientHost, Col_LoginType, IsPooled)
(SELECT @@SERVERNAME, @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'), ORIGINAL_LOGIN(), @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)'), @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)'), @LogonTriggerData.value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit'))
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trigLogon] ON ALL SERVER
This is all good and well, now we know who is logging onto our Server and when and from where. But this trigger firing can fill a large hard disk quickly. On a busy server there may be millions of entries into this Audit table daily. This is mostly as a result of connection pooling and how a connection is kept alive by an application server like IIS. So for the most part I don't worry to much about huge amounts of logons from pooled connections from application servers. The logon entries I'm more interested in are for non pooled connections.
So what we need, from an audit summary point of view is a distinct record for each logon from a distinct host and the logons pooled status, and the last time this logon logged in and how many times it logged in. This will give me some valuable information from the millions of records or raw logon data. Information like which logins have not logged in since Logon Auditing has been enabled, which generic logins are being used from which hosts and which non pooled logins are logging in the most. From my original mission of establishing which logins are used and how often, we can now determine this.
Now I need to take the daily logon audit information and summarize it and merge todays summarized information with the historic logon information. This is a very common scenario in a Data Warehouse environment. I NEED to summarize and TRUNCATE my AuditLogin table after I've summarized the data in it because of the huge amount of data that goes into this table every day. Knowing that login x logged on at a particular time yesterday is of little value. So I don't need the detail data, unless there is a security breach. Summarize, Merge to History, Truncate. Treat it mean, keep it clean.
The below code uses some incremental load logic I've gleened from my Data Warehouse experience. Basically the first full load and subsequent incremental loads of my AuditLoginHistory table use the same code. Thats pretty handy. I can run the same code to load the history table from the first load.
Basically each record in the history table is identified by Login, Host and Pooled. The two other fields are LoginCount and LastLoggedIn. So what the below code does is first summarize todays logon information. Then it compares it to the History information. If the same record exists, the LoginCount and LastLoggedIn is updated. If the record doesn't exist, a new one is inserted into the history table.
Summarizing Code:
with LoginCnt(ServerLogin, LoginCount, IsPooled, Host)
AS
(
select ServerLogin, count(*) AS LoginCount, IsPooled, Col_ClientHost from auditlogin
group by ServerLogin, IsPooled, Col_ClientHost
)
, LastLogin(ServerLogin, Host, LastLoggedIn, IsPooled)
AS
(
select serverlogin, Col_ClientHost, IsPooled, max(EventTime) AS LastLoggedIn from AuditLogin
group by ServerLogin, Col_ClientHost, IsPooled
)
login trigger
--SELECT lc.ServerLogin, lc.Host, ll.LastLoggedIn, lc.LoginCount, lc.IsPooled
--FROM LoginCnt lc INNER JOIN LastLogin ll
--ON lc.ServerLogin = ll.ServerLogin
--AND lc.Host = ll.Host
SELECT lc.ServerLogin, lc.Host, ll.LastLoggedIn, lc.LoginCount, lc.IsPooled
INTO #AuditTmp
FROM LoginCnt lc INNER JOIN LastLogin ll
ON lc.ServerLogin = ll.ServerLogin
AND lc.Host = ll.Host
ORDER BY ISPooled ASC, LoginCount DESC
GO
BEGIN TRAN
DECLARE @SL VARCHAR(128)
DECLARE @Host VARCHAR(30)
DECLARE @LastLoggedIn DATETIME
DECLARE @LC INT
DECLARE @IP BIT
Declare AuditCur CURSOR FOR
SELECT ServerLogin, Host, LastLoggedIn, LoginCount, IsPooled FROM #AuditTmp
OPEN AuditCur
FETCH NEXT FROM AuditCur INTO @SL, @Host, @LastLoggedIn, @LC, @IP
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM AuditLoginHistory
WHERE ServerLogin = @SL AND Host = @Host AND IsPooled = @IP) = 1
BEGIN
UPDATE AuditLoginHistory
SET LastLoggedIn = @LastLoggedIn,
LoginCount = LoginCount + @LC
WHERE ServerLogin = @SL AND Host = @Host AND IsPooled = @IP
END
ELSE
BEGIN
INSERT AuditLoginHistory(ServerLogin, Host, LastLoggedIn, LoginCount, IsPooled)
VALUES(@SL, @Host, @LastLoggedIn, @LC, @IP)
END
FETCH NEXT FROM AuditCur INTO @SL, @Host, @LastLoggedIn, @LC, @IP
END
CLOSE AuditCur
DEALLOCATE AuditCur
DROP TABLE #AuditTmp
TRUNCATE TABLE auditlogin
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
--PRINT 'Successful'
COMMIT TRAN
Below is the code for the two Audit tables:
USE [master]
GO
/****** Object: Table [dbo].[AuditLogin] Script Date: 01/19/2009 08:20:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditLogin]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AuditLogin](
[AuditEventId] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [nvarchar](100) NOT NULL,
[EventTime] [datetime] NOT NULL,
[ServerLogin] [nvarchar](100) NOT NULL,
[Col_ClientHost] [varchar](50) NULL,
[Col_LoginType] [varchar](50) NULL,
[IsPooled] [bit] NULL,
CONSTRAINT [PK_AuditLogin] PRIMARY KEY CLUSTERED
(
[AuditEventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
GRANT INSERT ON [dbo].[AuditLogin] TO [public]
GO
USE [master]
GO
/****** Object: Table [dbo].[AuditLoginHistory] Script Date: 04/06/2009 10:37:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AuditLoginHistory](
[ServerLogin] [varchar](128) NULL,
[Host] [varchar](30) NULL,
[LoginCount] [int] NULL,
[LastLoggedIn] [datetime] NULL,
[IsPooled] [bit] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Lastly, a caveat to make sure the trigger fires and doesn't cause logon failures: Ensure that all the objects that get created are accessible to public. Otherwise logons will fail because the user logging in causes the trigger to fire and the write to the Audit table is done in the context of the user thats logging on.
Thursday, April 2, 2009
Tracking database growth over time
We had a problem recently where our Sharepoint environment space requirements have grown beyond what was originally envisaged. The Sharepoint team wanted to know how the different SQL databases have grown over time.
Dang I thought, we don't record the size of a database at a particular point in time, over time. Then I thought, well we back the databases up frequently and we could look at the backup file sizes to get an indication of how big the database is, since there is a reasonable correlation between data, index and log space used and the backup size. But we only keep 2 weeks worth of backups on disk and to recall tapes for a year would be a mission. Then it dawned on me that the backup history tables in the msdb database would contain the database name, backup time and backup size!
Great, so I have my data source. The Backupset table just so happens to have 200000 records in it, with data for 80 databases. Well we can exclude all db's that aren't Sharepoint databases, and we can only look at type = 'D' for full database backups. But we still have daily full backups for each sharepoint database for the last 18 months. From a trending point of view, without being overwhelmed by masses of data, I want to see database growth by month. Ok so I want the 1st, or last backup for each database for each month for the last 18 months. Now there's a bit of T-SQL to write. I figured a couple of While loops would do the trick to loop through each month and year, and in those loops have a cursor get the backup info into a table variable for each database. Here is the code:
I'm sure there are other ways of doing this. Feel free to comment and add how you would do the above.
As a side, there is a post by Vyas Kondreddi posted in 2001 that does a similar thing, but doesn't summarize the size per database by month. It only returns all data for one database. Also, it reports size as the size of the mdf. If I create a sharepoint database with an initial size of 1GB and it takes 8 months to get to 1GB, then I won't have a true reflection of the actual size of the data, the size of the mdf with lots of empty space is academic. The actual data space used is important from a data growth point of view. Hence I use the backup_size field from the Backupset table.
The cool thing now is I can look at a database going back 18 months and see the size of the full backup at the end of each month. Now just pull this data into reporting services, have a series for each database and there we go! Database growth over time.
Dang I thought, we don't record the size of a database at a particular point in time, over time. Then I thought, well we back the databases up frequently and we could look at the backup file sizes to get an indication of how big the database is, since there is a reasonable correlation between data, index and log space used and the backup size. But we only keep 2 weeks worth of backups on disk and to recall tapes for a year would be a mission. Then it dawned on me that the backup history tables in the msdb database would contain the database name, backup time and backup size!
Great, so I have my data source. The Backupset table just so happens to have 200000 records in it, with data for 80 databases. Well we can exclude all db's that aren't Sharepoint databases, and we can only look at type = 'D' for full database backups. But we still have daily full backups for each sharepoint database for the last 18 months. From a trending point of view, without being overwhelmed by masses of data, I want to see database growth by month. Ok so I want the 1st, or last backup for each database for each month for the last 18 months. Now there's a bit of T-SQL to write. I figured a couple of While loops would do the trick to loop through each month and year, and in those loops have a cursor get the backup info into a table variable for each database. Here is the code:
DECLARE @Db_BackupHistory TABLE
(database_name VARCHAR(255),
backup_size BIGINT,
backup_start_date DATETIME)
DECLARE @Y INT
DECLARE @M INT
SET @Y = 2007
SET @M = 1
WHILE @Y < 2010
BEGIN
WHILE @M < 13
BEGIN
DECLARE @DB VARCHAR(255)
DECLARE CurDB CURSOR FOR
SELECT DISTINCT database_name FROM backupset where database_name like 'Sharepoint_%'
OPEN CurDB
FETCH NEXT FROM CurDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @Db_BackupHistory(database_name, backup_size, backup_start_date)
select TOP 1 database_name, backup_size, backup_start_date from backupset
WHERE database_name LIKE @DB and type = 'D'
and DATEPART(mm, backup_start_date) = @M and DATEPART(yy, backup_start_date) = @Y
order by backup_start_date DESC
FETCH NEXT FROM CurDB INTO @DB
END
CLOSE CurDB
DEALLOCATE CurDB
SET @M = @M + 1
END
SET @M = 1
SET @Y = @Y + 1
END
select database_name, backup_size/1024/1024 AS [BackupSize(MB)], backup_start_date from @Db_BackupHistory
order by database_name, backup_start_date
I'm sure there are other ways of doing this. Feel free to comment and add how you would do the above.
As a side, there is a post by Vyas Kondreddi posted in 2001 that does a similar thing, but doesn't summarize the size per database by month. It only returns all data for one database. Also, it reports size as the size of the mdf. If I create a sharepoint database with an initial size of 1GB and it takes 8 months to get to 1GB, then I won't have a true reflection of the actual size of the data, the size of the mdf with lots of empty space is academic. The actual data space used is important from a data growth point of view. Hence I use the backup_size field from the Backupset table.
The cool thing now is I can look at a database going back 18 months and see the size of the full backup at the end of each month. Now just pull this data into reporting services, have a series for each database and there we go! Database growth over time.
Subscribe to:
Posts (Atom)