As we know Microsoft does not support compression for SQL Server Express but time to time we use express edition database engine when all you need is small database that might not even grow more than 1 gig or 5 gigs.
we need 7z zip program its an opensource software will help us compress the files, download the latest version 64 bit (http://www.7-zip.org/)
Enable xp_cmdShell ( we are going to use xp_cmdshell to execute the batch command to compress or delete files)
TSQL Script( Where Magic happens)
How does script work: it first looks at mypath and lists all .bak files and folders it loops each of these files using a cursor and execute the 7zip command to compress the .bak to .zip and cleanuphrs parameter will clean the backup zip files older than specified number of hours.
As part of database life cycle management process organizations ensure database is patched and kept up to date to ensure database system is secured from any vulnerabilities and to stay in support life cycle.
Microsoft Support Life cycle provides a consistent and predictable guidelines for product support availability when a product releases and throughout that product’s life. By understanding the product support available, customers are will be able to maximize the management of their IT investments and strategically plan for a successful IT future.
To know the current status of Microsft life cycle is not easy, MS provides an Excel file that contains all the build versions together with their current support life cycle stage for 2005 through the current version is available.(please note as we speak MS ended support for 2005)
So put to together a powershell script that will load the excel file into SQL Server table.
Powershell Script to load the excel
2) First things first, Ensure CLR Integration is enabled at instance level.
3) Ensure Appropriate CLR Integration Code Access Security levels are set when creating assembly’s in SQL Server
Please refer to msdn documentation here https://msdn.microsoft.com/en-us/library/ms345101.aspx
4) Ensure the database is setup correctly
If you are using assemblies with External access and Unsafe the database property TRUSTWORTHY needs to be turned on.
Refer to MSDN documentation on TRUSTWORTHY https://msdn.microsoft.com/en-us/library/ms187861.aspx
When you are setting up alerts using WMI and if your intention is to capture the event data with sql agent job make sure you change below settings in SQL Server Agent Configuration.
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1 GO
This will allow SQL Server Agent to replace the tokens with running values (what database changed, who changed it etc.). Read more about using Tokens in Job Steps here (make sure you read the security note in the article and understand the security risk).
Based on MS documentation this is a quick way to create a copy of database (only Schema) including statistics and Indexes of source database this was released in SQL 14 service pack 2.
When this command is issued SQL Server creates an internal snapshot of source database just like how it creates for checkdb and drops this snapshot when the cloning process is done but during the cloning process it holds a shared lock on source database and X lock on target database and it leaves target database in read only mode although you can change the state of the target database if you intent to add data or modify.
So why do you need to clone database.
According to MS “DBCC CLONEDATABASE should be used to create a schema and statistics only copy of a production database in order to investigate query performance issues.”. The original intention of the feature is to diagnose any performance issues of a production database with out needing to effect the production database. Although this is so late in the game its never to late for new feature.
Don’t confuse with Database Snapshots that’s totally different concept.
Bottom line: Saves a lot of time when you are debugging execution plans and performance related issues.
My colleague reported to me that one of our database server is reporting consistent high CPU usage so I looked at it I noticed CPU was at 100% from last one week when I contacted the application owner and I foundthat they implemented a new feature that polls the database for every second to ensure the data collection process is running properly as it was necessary to ensure that we are under compliance in terms of reporting and auditing. So I ran a query to pull the queries with high cpu utilization with execution count. I certainly noticed a query running more often with high cpu usage.
I know that above highlighted query is causing the high cpu usage, next I looked at query stats and noticed this query is running twice every second, so I looked at the plan
Select top 1 col1 from table order by 1
Table is clustered and col1 is not part of clustered index and does not have an index. simple enough SQL server decides to do Clustered index scan and sorts(fully blocking) col1 and selects 1 row with no predicate SQL server doesn’t think its missing an index.
SQL Server Execution Times: with out non clustered index
CPU time = 2296 ms, elapsed time = 658 ms.
So I created a non clustered index on col1 in desc on the table
SQL Server Execution Times: with non clustered index
CPU time = 0 ms, elapsed time = 0 ms.
Cpu Usage dramatically reduced
Its very important to understand no matter how much physical resources you might have on a server its very important understand that one bad query can literally bring the server down to it knees.