GAPTHEGURU

Geek with special skills

Use PowerShell to gather SQL Server database physical file sizes

Problem

I am hired as an DBA at a customer and one of the SQL servers has a database that grows very rapidly (1 GB per week). I needed an easy way to monitor the growth of this database. I also needed an easy way to monitor all my servers for growth trending.

Solution

I know there are tools that will monitor database file size, but I wanted something easy and something I could use to gather and retain historical data. It also had to be free. After a little research I decided to try using PowerShell. I did some searching on the internet and found some sample scripts for connecting to servers, getting data from those servers and then writing the data to an Excel spreadsheet.

Prepare the environment

Before we start let me say that I am not a PowerShell guru, so some of the terminology I use may not be exact. The first thing you will need to do is start PowerShell. In Windows 7 click the Start button and type PowerShell in the Search field. When I do this on my system I am presented with a couple choices. The first two items in the list will start PowerShell, Windows PowerShell ISE is a GUI interface and Windows PowerShell is the command line interface. I like to use Windows PowerShell ISE.

Program Selection

The next thing you will need to do to use PowerShell on your system is to change the execution policy. By default the execution policy is set to Restricted, this means that no scripts will run, not any at all. To check what the current execution policy is set to, open PowerShell and enter the command Get-ExecutionPolicy. I have set my execution policy to RemoteSigned, this allows scripts I have written to run and to run scripts from the internet only if those scripts have been signed by a trusted publisher. To set the execution policy enter the command Set-ExecutionPolicy RemoteSigned. Note: there are other options for execution policy, check the documentation for the appropriate policy. Now that I have the execution policy set I am ready to go.

What I wanted to accomplish was to look at all my SQL servers and get the actual data file and log file size in megabytes. The script shown below reads a .txt file that lists all my SQL servers and gets the file name, file path, file size, and used size for both data file (.mdf) and log file (.ldf) for each database on each server in the .txt file. The data is then written to a spreadsheet on my local machine.

#Create a new Excel object using COM 
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$intRow = 1
 ForEach ($instance in Get-Content "C:\Users\dkelly\Documents\PowershellScripts\sqlservers.txt")
    {
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
    $dbs=$s.Databases

     $intRow++
     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "Server: $s"
     $Sheet.Cells.Item($intRow,1).Font.Size = 12
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True

     $intRow++
     $Sheet.Cells.Item($intRow,2) = "Database"
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True
     $Sheet.Cells.Item($intRow,3) = "Data Name"
     $Sheet.Cells.Item($intRow,3).Font.Bold = $True
     $Sheet.Cells.Item($intRow,4) = "Data File"
     $Sheet.Cells.Item($intRow,4).Font.Bold = $True
     $sheet.Cells.Item($intRow,5) = "Data Size (MB)"
     $Sheet.Cells.Item($intRow,5).Font.Bold = $True
     $Sheet.Cells.Item($intRow,6) = "Data Used Space (MB)"
     $Sheet.Cells.Item($intRow,6).Font.Bold = $True
     $Sheet.Cells.Item($intRow,7) = "Log Name"
     $Sheet.Cells.Item($intRow,7).Font.Bold = $True
     $Sheet.Cells.Item($intRow,8) = "Log Size (MB)"
     $Sheet.Cells.Item($intRow,8).Font.Bold = $True
     $Sheet.Cells.Item($intRow,9) = "Log Used Space (MB)"
     $Sheet.Cells.Item($intRow,9).Font.Bold = $True
     $Sheet.Cells.Item($intRow,10) = "Log File"
     $Sheet.Cells.Item($intRow,10).Font.Bold = $True

    foreach ($db in $dbs) 
        {

          $dbname = $db.Name
          $fileGroups = $db.FileGroups

          ForEach ($fg in $fileGroups)
            {
       #   write-host $fg.files | select name
            If ($fg) 
                {

                    $intRow++

                    $mdfInfo = $fg.Files | Select Name, FileName, size, UsedSpace
                    $Sheet.Cells.Item($intRow,2) = $dbname
                    $Sheet.Cells.Item($intRow,3) = $mdfInfo.Name
                    $Sheet.Cells.Item($intRow,4) = $mdfInfo.FileName
                    $Sheet.Cells.Item($intRow,5) = ($mdfInfo.size / 1000)
                    $Sheet.Cells.Item($intRow,6) = ($mdfInfo.UsedSpace / 1000)

                    $logInfo = $db.LogFiles | Select Name, FileName, Size, UsedSpace
                    $Sheet.Cells.Item($intRow,7) = $logInfo.Name
                    $Sheet.Cells.Item($intRow,8) = ($logInfo.Size / 1000)
                    $Sheet.Cells.Item($intRow,9) = ($logInfo.UsedSpace / 1000)
                    $Sheet.Cells.Item($intRow,10) = $logInfo.FileName
                }
            }
        }
        $intRow++
    }
    $Sheet.UsedRange.EntireColumn.AutoFit()

The sqlserver.txt file is just a list of server names, one name per line with no punctuation as shown here;

testserver1 testserver2 testserver3

The first four lines in the script create the Excel spreadsheet. Then for each server in the sqlserver.txt file we use the Microsoft.SQLServer.Management.SMO.Server class to create a server object of SQL Server. From the server object we can get the databases, $dbs=$s.Databases, on each server. The next several lines in the script set the values in the columns of the header row in the spreadsheet. The section of the script starting with foreach ($db in $dbs) is the part of the script that actually gets the database file data and writes it to the spreadsheet.

Shown below is the resulting spreadsheet. I broke the spreadsheet image into 3 images for readability.

The first four lines in the script create the Excel spreadsheet
The next several lines in the script set the values in the columns of the header row in the spreadsheet
the resulting spreadsheet

In my next tip I will describe the process for getting the data into a database using SSIS then reporting on that data. Please stayed tuned.

Advertisements

05/23/2012 Posted by | Powershell, Sql Server | , | 2 Comments

SQL Server Database Mirroring Breaks if Endpoint Owner is Deleted

SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

Problem

Will SQL Server database mirroring stop working if the user used to create the endpoints is removed?  In this tip I will walk through the steps to find out.

Solution

Before I am able to provide with a definitive answer to the above problem, I created the following test environment.

  • A Windows 2008 VM (Virtual Server) named SQLTEST1 with SQL 2008 Developer installed
  • A Windows 2008 VM (Virtual Server) named SQLTEST2 with SQL 2008 Developer installed
  • Since these servers are not in a domain I created a Windows user WinUser on both VMs and used this account to setup mirroring.
  • I created a test database called mytest and mirrored it between the two SQL Servers SQLTEST1 and SQLTEST2.

Check Mirroring Status

Let’s start Database Mirroring Monitor to verify that database mirroring is working. Below we can see that the databases are synchronized.

use database mirroring monitor to check status

Drop SQL Server Login

Now, let’s try to drop SQLTEST1\WinUser login by executing the following. As expected, the SQL login cannot be dropped because it owns the Mirroring endpoint. Since this failed I did not bother to try this on SQLTEST2.

tsql code to drop sql server login

Drop Windows User

Now let’s try to drop the Windows user WinUser on both servers.  This was successful on both servers.

steps to delete a windows login

Check Mirroring Status

Now that WinUser has been deleted on both servers, let’s check if database mirroring is still working.  With a bit of surprise, database Mirroring is still working. To further verify it, I created tables and inserted data on the mytest database on the principle server SQLTEST1 and verified that such information was successfully replicated to the mirroring server SQLTEST2.

use database mirroring monitor to check status

Restart SQL Server

Now, let’s restart the SQL Server service on both servers.

restart sql server services

Check Mirroring Status

If we check the mirroring status again we can see that it has failed.

use database mirroring monitor to check status

Conclusion

In order to avoid a possible mirroring outage it is appropriate to verify that a Windows user does not own mirroring endpoints, because if the user is removed from Active Directory or a local group, mirroring will break the next time the server is restarted.  If you do use a Windows user you need to make sure that your accounts are well documented and not accidently deleted.

05/23/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , | Leave a comment

SQL SERVER VERSIONS


I’m continually trying to track down what service packs are installed on various SQL Servers I support. I can never find the right support page on Microsoft’s site. So here’s an article with all the SQL Server version information I can track down. If you know of any older versions or can help me fill out any missing data, please post in the comments and I’ll update the article.

Article Body

SQL Server 2008 R2
10.50.2789.0 SQL Server 2008 R2 SP1 CU3 17 Oct 2011
10.50.2772.0 SQL Server 2008 R2 SP1 CU2 16 Aug 2011
10.50.2769.0 SQL Server 2008 R2 SP1 CU1 16 Sep 2011
10.50.2500.0 SQL Server 2008 R2 SP1 11 Jul 2011
10.50.1807.0 SQL Server 2008 R2 CU10 19 Oct 2011
10.50.1804.0 SQL Server 2008 R2 CU9 23 Aug 2011
10.50.1797.0 SQL Server 2008 R2 CU8 16 Sep 2011
10.50.1777.0 SQL Server 2008 R2 CU7 16 Jun 2011
10.50.1765.0 SQL Server 2008 R2 CU6 21 Feb 2011
10.50.1753.0 SQL Server 2008 R2 CU5 20 Dec 2010
10.50.1746.0 SQL Server 2008 R2 CU4 18 Oct 2010
10.50.1734.0 SQL Server 2008 R2 CU3 17 Aug 2010
10.50.1720.0 SQL Server 2008 R2 CU2 25 Jun 2010
10.50.1702.0 SQL Server 2008 R2 CU1 18 May 2010
10.50.1600.1 SQL Server 2008 R2 RTM 12 Apr 2010
SQL Server 2008
10.00.5768 SQL Server 2008 SP3 CU2 22 Nov 2011
10.00.5766 SQL Server 2008 SP3 CU1 18 Oct 2011
10.00.5500 SQL Server 2008 SP3 6 Oct 2011
10.00.4323 SQL Server 2008 SP2 CU7 21 Nov 2011
10.00.4321 SQL Server 2008 SP2 CU6 20 Sep 2011
10.00.4316 SQL Server 2008 SP2 CU5 18 Jul 2011
10.00.4285 SQL Server 2008 SP2 CU4 16 May 2011
10.00.4279 SQL Server 2008 SP2 CU3 21 Mar 2011
10.00.4272 SQL Server 2008 SP2 CU2 17 Jan 2011
10.00.4266 SQL Server 2008 SP2 CU1 15 Nov 2010
10.00.4000 SQL Server 2008 SP2 29 Sep 2010
10.00.2850 SQL Server 2008 SP1 CU16 19 Sep 2011
10.00.2847 SQL Server 2008 SP1 CU15 18 Jul 2011
10.00.2816 SQL Server 2008 SP1 CU13 22 Mar 2011
10.00.2812 SQL Server 2008 SP1 CU14 16 May 2011
10.00.2808 SQL Server 2008 SP1 CU12 17 Jan 2011
10.00.2804 SQL Server 2008 SP1 CU11 15 Nov 2010
10.00.2799 SQL Server 2008 SP1 CU10 21 Sep 2010
10.00.2789 SQL Server 2008 SP1 CU9 19 Jul 2010
10.00.2775 SQL Server 2008 SP1 CU8 17 May 2010
10.00.2766 SQL Server 2008 SP1 CU7 15 Mar 2010
10.00.2757 SQL Server 2008 SP1 CU6 18 Jan 2010
10.00.2746 SQL Server 2008 SP1 CU5 24 Nov 2009
10.00.2734 SQL Server 2008 SP1 CU4 22 Sep 2009
10.00.2723 SQL Server 2008 SP1 CU3 21 Jul 2009
10.00.2714 SQL Server 2008 SP1 CU2 18 May 2009
10.00.2710 SQL Server 2008 SP1 CU1 16 Apr 2009
10.00.2531 SQL Server 2008 SP1 7 Apr 2009
10.00.1835 SQL Server 2008 RTM CU10 15 Mar 2010
10.00.1828 SQL Server 2008 RTM CU9 18 Jan 2009
10.00.1823 SQL Server 2008 RTM CU8 16 Nov 2009
10.00.1818 SQL Server 2008 RTM CU7 21 Sep 2009
10.00.1812 SQL Server 2008 RTM CU6 21 Jul 2009
10.00.1806 SQL Server 2008 RTM CU5 18 May 2009
10.00.1798 SQL Server 2008 RTM CU4 17 Mar 2009
10.00.1787 SQL Server 2008 RTM CU3 19 Jan 2009
10.00.1779 SQL Server 2008 RTM CU2 17 Nov 2008
10.00.1763 SQL Server 2008 RTM CU1 22 Sep 2008
10.00.1600 SQL Server 2008 RTM 6 Aug 2008
SQL Server 2005
9.00.5266 SQL Server 2005 SP4 CU3 21 Mar 2011
9.00.5259 SQL Server 2005 SP4 CU2 22 Feb 2011
9.00.5254 SQL Server 2005 SP4 CU1 20 Dec 2010
9.00.5000 SQL Server 2005 SP4 17 Dec 2010
9.00.4325 SQL Server 2005 SP3 CU15 21 Mar 2011
9.00.4317 SQL Server 2005 SP3 CU14 21 Feb 2011
9.00.4315 SQL Server 2005 SP3 CU13 20 Dec 2010
9.00.4311 SQL Server 2005 SP3 CU12 18 Oct 2010
9.00.4309 SQL Server 2005 SP3 CU11 17 Aug 2010
9.00.4305 SQL Server 2005 SP3 CU10 23 Jun 2010
9.00.4294 SQL Server 2005 SP3 CU9 19 Apr 2010
9.00.4285 SQL Server 2005 SP3 CU8 16 Feb 2010
9.00.4273 SQL Server 2005 SP3 CU7 21 Dec 2009
9.00.4266 SQL Server 2005 SP3 CU6 19 Oct 2009
9.00.4230 SQL Server 2005 SP3 CU5 17 Aug 2009
9.00.4226 SQL Server 2005 SP3 CU4 16 Jun 2009
9.00.4220 SQL Server 2005 SP3 CU3 21 Apr 2009
9.00.4211 SQL Server 2005 SP3 CU2 17 Feb 2009
9.00.4207 SQL Server 2005 SP3 CU1 20 Dec 2008
9.00.4053 SQL Server 2005 SP3 GDR (Security Update) 13 Oct 2009
9.00.4035 SQL Server 2005 SP3 16 Dec 2008
9.00.3356 SQL Server 2005 SP2 CU17 21 Dec 2009
9.00.3355 SQL Server 2005 SP2 CU16 19 Oct 2009
9.00.3330 SQL Server 2005 SP2 CU15 18 Aug 2009
9.00.3328 SQL Server 2005 SP2 CU14 16 Jun 2009
9.00.3325 SQL Server 2005 SP2 CU13 21 Apr 2009
9.00.3315 SQL Server 2005 SP2 CU12 17 Feb 2009
9.00.3310 SQL Server 2005 SP2 Security Update 10 Feb 2009
9.00.3301 SQL Server 2005 SP2 CU11 15 Dec 2008
9.00.3294 SQL Server 2005 SP2 CU10 20 Oct 2008
9.00.3282 SQL Server 2005 SP2 CU9 18 Aug 2008
9.00.3257 SQL Server 2005 SP2 CU8 16 Jun 2008
9.00.3239 SQL Server 2005 SP2 CU7 14 Apr 2008
9.00.3233 SQL Server 2005 QFE Security Hotfix 8 Jul 2008
9.00.3228 SQL Server 2005 SP2 CU6 18 Feb 2008
9.00.3215 SQL Server 2005 SP2 CU5 17 Dec 2007
9.00.3200 SQL Server 2005 SP2 CU4 15 Oct 2007
9.00.3186 SQL Server 2005 SP2 CU3 20 Aug 2007
9.00.3175 SQL Server 2005 SP2 CU2 28 Jun 2007
9.00.3161 SQL Server 2005 SP2 CU1 15 Apr 2007
9.00.3152 SQL Server 2005 SP2 Cumulative Hotfix 7 Mar 2007
9.00.3077 SQL Server 2005 Security Update 10 Feb 2009
9.00.3054 SQL Server 2005 KB934458 5 Apr 2007
9.00.3042.01 SQL Server 2005 “SP2a” 5 Mar 2007
9.00.3042 SQL Server 2005 SP2 1 Feb 2007
9.00.2047 SQL Server 2005 SP1
9.00.1399 SQL Server 2005 RTM 1 Nov 2005
SQL Server 2000
8.00.2039 SQL Server 2000 SP4
8.00.0760 SQL Server 2000 SP3
8.00.0534 SQL Server 2000 SP2
8.00.0384 SQL Server 2000 SP1
8.00.0194 SQL Server 2000 RTM
SQL Server 7
7.00.1063 SQL Server 7 SP4
7.00.0961 SQL Server 7 SP3 15 Dec 2000
7.00.0842 SQL Server 7 SP2 20 Mar 2000
7.00.0699 SQL Server 7 SP1 15 Jul 1999
7.00.0623 SQL Server 7 RTM
SQL Server 6.5
6.50.416 SQL Server 6.5 SP5a
6.50.415 SQL Server 6.5 SP5
6.50.281 SQL Server 6.5 SP4
6.50.258 SQL Server 6.5 SP3
6.50.240 SQL Server 6.5 SP2
6.50.213 SQL Server 6.5 SP1
6.50.201 SQL Server 6.5 RTM

You can determine what version SQL Server is running by running

Select @@version

@@Version is a system level variable that holds the current version. On my computer this returns

Microsoft SQL Server  2000 - 8.00.384 (Intel X86) 
	May 23 2001 00:02:52 
	Copyright (c) 1988-2000 Microsoft Corporation
	Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

The main version number is 8.00.384 which corresponds to SQL Server 2000 SP1. See below for a complete list of versions. It will also tell us the version of the operating system we’re running. In this case I’m running Windows 2000 (aka NT 5.0) Service Pack 2. You can find this same information in Enterprise Manager by right clicking on a server and choosing Properties. The version information is displayed in the General tab.

This information is pulled from the system extended procedure xp_msver. You can call this stored procedure like

exec master..xp_msver

and it returns

Index  Name                             Internal_Value Character_Value                     

------ -------------------------------- -------------- ------------------------------
1      ProductName                      NULL           Microsoft SQL Server
2      ProductVersion                   524288         8.00.384
3      Language                         1033           English (United States)
4      Platform                         NULL           NT INTEL X86
5      Comments                         NULL           NT INTEL X86
6      CompanyName                      NULL           Microsoft Corporation
7      FileDescription                  NULL           SQL Server Windows NT
8      FileVersion                      NULL           2000.080.0384.00
9      InternalName                     NULL           SQLSERVR
10     LegalCopyright                   NULL           © 1988-2000 Microsoft ...
11     LegalTrademarks                  NULL           Microsoft® is a registered ...
12     OriginalFilename                 NULL           SQLSERVR.EXE
13     PrivateBuild                     NULL           NULL
14     SpecialBuild                     25165824       NULL
15     WindowsVersion                   143851525      5.0 (2195)
16     ProcessorCount                   1              1
17     ProcessorActiveMask              1              00000001
18     ProcessorType                    586            PROCESSOR_INTEL_PENTIUM
19     PhysicalMemory                   255            255 (267902976)
20     Product ID                       NULL           NULL

03/16/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , , | Leave a comment

Monitoring tempdb Transactions and Space usage

As the tempdb database is the common global resource for all the operations going on in SQL Server, so the DBA has to be bit cautious about the use of it. Because any unexpected operations by the applications running under the SQL Server instance or any adhoc query by the user can eat all the space available to the tempdb database resulting decrease in the performance of other applications running under the same instance. So it is necessary to keep track the usage of tempdb database by various applications and processes and to take necessary actions when the size falls down below the threshold limit. Monitoring tempdb over time will help in determining the optimal size of the tempdb

Use the below query to check the current tempdb size:

 

02/16/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , , | 1 Comment

SQL SERVER – Rollback TRUNCATE Command in Transaction

If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

Truncate *IS* a logged operation, it just doesn’t log removing the records, it logs the page deallocations.

The code to simulate above result is here.

USE tempdb

GO

-- Create Test Table

CREATE TABLE TruncateTest (ID INT)

INSERT INTO TruncateTest (ID)

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

GO

-- Check the data before truncate

SELECT * FROM TruncateTest

GO

-- Begin Transaction

BEGIN TRAN

-- Truncate Table

TRUNCATE TABLE TruncateTest

GO

-- Check the data after truncate

SELECT * FROM TruncateTest

GO

-- Rollback Transaction

ROLLBACK TRAN

GO

-- Check the data after Rollback

SELECT * FROM TruncateTest

GO

-- Clean up

DROP TABLE TruncateTest

GO

Example from sql authority:

Following example demonstrates how during the transaction truncate can be rolled back.

 

01/31/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , , , , | 2 Comments

   

%d bloggers like this: