Bart's Weblog

Just a blog…

Archive for the ‘SQL’ Category

Microsoft SQL Server 2005: Display Fragmentation Information of Data and Indexes of Database Table

Posted by bartvdw on 0202/0505/2013

SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table
http://blog.sqlauthority.com/2008/01/10/sql-server-2005-display-fragmentation-information-of-data-and-indexes-of-database-table/

Posted in Microsoft, SQL | Leave a Comment »

VMware KB: Purging old data from the database used by vCenter Server (1025914)

Posted by bartvdw on 1919/0303/2013

http://kb.vmware.com/kb/1025914

Posted in SQL, vCenter, VMware, vSphere | Leave a Comment »

Microsoft SQL Server: Backup database to a mapped drive

Posted by bartvdw on 0505/1010/2012

This is very useful in some cases: backup a Microsoft SQL Server database to a mapped drive… Resources at the bottom with more details.

You can do this by mapping the drive from within SQL, open a new query and use the commands below (examples):

> Map y: drive: EXEC xp_cmdshell ‘net use y: \\server\share /user:usr passw’
> Remove y: drive: EXEC xp_cmdshell ‘net use y: /d’

Interesting to know is that ‘xp_cmdshell’ is disabled by default (for sure SQL Server 2005, didn’t check for other versions). You can enable (or disable) using following query:

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

(to disable, change value xp_cmdshell to 0 ofcourse)

How to backup SQL Server databases to a mapped drive
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/02/27/how-to-backup-sql-server-databases-to-a-mapped-drive.aspx

Enabling xp_cmdshell in SQL Server 2005
http://weblogs.sqlteam.com/tarad/archive/2006/09/14/12103.aspx

Posted in Microsoft, SQL | Leave a Comment »

VMware vCenter SQL Express database reached maximum database size

Posted by bartvdw on 2929/0505/2012

Recently had a customer who’s SQL Express database for VMware vCenter reached the maximum database size. This made vCenter unavailable… It’s very simple to fix though!

Purging old data from the database used by vCenter Server
http://kb.vmware.com/kb/1025914

SQL Server Express
http://en.wikipedia.org/wiki/SQL_Server_Express

SQL Server 2008 R2 Express Database Size Limit Increased to 10GB
http://blogs.msdn.com/b/sqlexpress/archive/2010/04/21/database-size-limit-increased-to-10gb-in-sql-server-2008-r2-express.aspx

Posted in SQL, VMware | 1 Comment »

SQL Server: Shrinking SQL transaction log

Posted by bartvdw on 1313/0303/2011

Had an issue recently regarding shrinking SQL transaction log of a system DB. Short information about below, full in the link at the bottom (Tibor Karaszi). Really helped me in understanding and solving my issue!

By default the model system DB is configured with recovery model Full. This makes that it has a transaction log that will grow in time… Below how to accomplish shrinking use SQL Query, which gives you best info:

  • Simple recovery model
    USE dbname
    CHECKPOINT
    –First param below is fileno for log file, often 2. Check with sys.database_files
    –Second is desired size, in MB.
    DBCC SHRINKFILE(2, 500)
    DBCC SQLPERF(LOGSPACE) –Optional
    DBCC LOGINFO –Optional
    Now repeat above commands as many times as needed!
  • Full or bulk_logged recovery model
    USE dbname
    BACKUP LOG dbname TO DISK = ‘C:\x\dbname.trn’
    –First param below is fileno for log file, often 2. Check with sys.database_files
    –Second is desired size, in MB.
    DBCC SHRINKFILE(2, 500)
    DBCC SQLPERF(LOGSPACE) –Optional
    DBCC LOGINFO –Optional
    Now repeat above commands as many times as needed!

 

Recovery Models for System Databases
http://msdn.microsoft.com/en-us/library/ms365937%28v=sql.90%29.aspx

Why you want to be restrictive with shrink of database files
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

System Databases in SQL Server
http://www.sql-server-performance.com/articles/dba/System_Databases_in_SQL_Server_p1.aspx

SQL Server 2005 Recovery Models
http://dbarecovery.com/backup-and-recovery-overview/sql-server-2005/sql-server-2005-recovery-models.html

Posted in SQL | 1 Comment »

Antivirus software exclusions

Posted by bartvdw on 1313/0808/2008

When you configure antivirus software for servers, you need to take exclusions into account. And for MS products, they are fairly good documented. If you don’t add these exclusions, you could get trouble/errors. For applications not listed here (MS or third party), always verify if you need to exclude something to make sure your antivirus software does not affect your application!

Below a summary of such exclusions and references to MS articles describing these exclusions. I will update this post in case I have additional information.

Note: In the list below, default file locations are used. If you have change the location of the files (ex. Ntds.dit), you need to use the altered path obviously!!

General exclusions Windows Server 2003, Windows 2000, Windows XP, or Windows Vista:

  • %windir%\ntfrs
  • %windir%\SoftwareDistribution\Datastore\Datastore.edb
  • %windir%\SoftwareDistribution\Datastore\Logs\Edb*.log
  • %windir%\SoftwareDistribution\Datastore\Logs\Res1.log
  • %windir%\SoftwareDistribution\Datastore\Logs\Res2.log
  • %windir%\SoftwareDistribution\Datastore\Logs\Edb.chk
  • %windir%\SoftwareDistribution\Datastore\Logs\Tmp.edb
  • For Windows 2000 & 2003 DC’s
    • %windir%\ntds\Ntds.dit
    • %windir%\ntds\Ntds.pat
    • %windir%\ntds\EDB*.log
    • %windir%\ntds\Res1.log
    • %windir%\ntds\Res2.log
    • %windir%\ntds\Temp.edb
    • %windir%\ntds\Edb.chk
    • %systemroot%\sysvol (only this folder, not all subfolders!!!)
    • %systemroot%\sysvol\domain\DO_NOT_REMOVE_NtFrs_PreInstall_Directory
    • %systemroot%\sysvol\staging
    • %systemroot%\sysvol\staging areas
    • %systemroot%\sysvol\sysvol
  • Clusters:
    • %windir%\Cluster
    • Q:\ (quorum)
  • DHCP: %windir%\system32\dhcp
  • DNS: %windir%\system32\dns
  • WINS: %windir%\system32\wins

Exchange Server:

  • Cdb.exe
  • Cidaemon.exe
  • Store.exe
  • Emsmta.exe
  • Mad.exe
  • Mssearch.exe
  • Inetinfo.exe
  • W3wp.exe
  • Exchsrvr\Conndata
  • Exchsrvr\Mailroot
  • Exchsrvr\Mdbdata
  • Exchsrvr\Mtadata
  • Exchsrvr\server_name.log
  • Exchsrvr\Srsdata
  • %systemroot%\IIS Temporary Compressed Files
  • %SystemRoot%\System32\Inetsrv
  • All .edb; .stm (on Exchange 2000 Server); .log Exchange files
  • M: drive (on Exchange 2000 Server)
  • SBS:
    • C:\Program Files\Microsoft Windows Small Business Server\Networking\POP3\Failed Mail
    • C:\Program Files\Microsoft Windows Small Business Server\Networking\POP3\Incoming Mail

SQL Server: SQL Server data files that have the .mdf extension, the .ldf extension, and the .ndf extension

WSUS: MSSQL$WSUS and WSUS content directory

References:

Virus scanning recommendations for computers that are running Windows Server 2003, Windows 2000, Windows XP, or Windows Vista
http://support.microsoft.com/kb/822158

Overview of Exchange Server 2003 and antivirus software
http://support.microsoft.com/kb/823166

Guidelines for choosing antivirus software to run on the computers that are running SQL Server
http://support.microsoft.com/kb/309422

Recommended Forefront Client Security file and folder exclusions for Microsoft products
http://support.microsoft.com/kb/943556

Multiple symptoms occur if an antivirus scan occurs while the Wsusscan.cab file or the Wsusscn2.cab file is copied
http://support.microsoft.com/kb/900638

Posted in Exchange, McAfee, SBS, Security, SQL, Windows | 3 Comments »

How to identify Microsoft SQL Server version & build…

Posted by bartvdw on 3030/0303/2008

Some time ago I needed to easily find the SQL version & build installed on a machine to identify missing patches. Below the links that will tell you everything you need about this!

Microsoft SQL Server 2008, 2005, 2000 and 7.0 Builds
http://sqlserverbuilds.blogspot.com/

How to identify your SQL Server version and edition
http://support.microsoft.com/kb/321185/

Also some registry keys that could be easy when you write custom scripts.

SQL Server 2005 Express
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer\CurrentVersion — CurrentVersion — REG_SZ

Posted in SQL | Leave a Comment »

Allocated memory issue: sqlservr.exe

Posted by bartvdw on 2626/0101/2008

Recently I found on a SBS 2003 box a memory issue. After a quick look in my Task Manager I found that the process was called sqlservr.exe

Next step was finding the service linked to the process. “tlist.exe -s” (included in Debugging Tools for Windows) gave me the information needed: MSSQL$SBSMONITORING

What to do about that? Well after searching a little bit, I found this solution:

  • Open cmd
  • osql -E -S YOURSERVERNAME\sbsmonitoring [hit enter]
  • sp_configure ‘show advanced options’,1 [hit enter]
  • reconfigure with override [hit enter]
  • go [hit enter]
  • sp_configure ‘max server memory’, 70 [hit enter] (based on the information I found, this is OK)
  • reconfigure with override [hit enter]
  • go [hit enter]

Note: mind the quotes used! I have problems with publishing them correctly, so if you receive an error pointing into that direction, correct them manually in your syntax.

Cool link that helped me a lot:
http://msmvps.com/blogs/bradley/archive/2005/02/04/34984.aspx

 

Update

Troubleshooting High Memory Usage by a MSDE Instance.
http://blogs.technet.com/asksbs/archive/2008/07/05/troubleshooting-high-memory-usage-by-a-msde-instance.aspx

Adjusting SQL embedded instances
http://msmvps.com/blogs/bradley/archive/2007/12/25/adjusting-sql-embedded-instances.aspx

Thanks again Susan for the great post on this subject !!!

Posted in SBS, SQL | 1 Comment »