There may be several methods to delete the files on disk from within SQL Server. Please click here to read a very good article about using the FORFILES Command to Delete files. It is flexible command and may be modified according to any environment.
In a recent task i was connected to SQL Server through SSMS and had no OS access. To configure the automatic backups i was required to create couple of directories on the disk. So without having OS access i had to create directories by using T-SQL. Following is the command that i used to create ‘D:DBBackupsDBDigger’
EXEC master.dbo.xp_create_subdir 'D:DBBackups_DBDIgger' GO
Always there exists a single log file for each SQL Server database. Log file keeps growing when in full or bulk logged recovery model. DBAs implement strategies to manage the log file size. There are two aspects of log file size.
- One is log file size that it captures on disk and is shown at OS level.
- Second aspect relates to filled part of over all log file.
Suppose there is a log file with size 20 GB on disk then it is quite possible that it is filled with only 1 GB log and rest of 19 GB is empty. SQL Server will keep populating the remaining part unless it is full and then will claim more size according to file growth settings.
It is a basic requirement to have an idea of log file and its used space. Following is a very simple way to get this information. You may also insert the information into a table on daily or weekly basis and may process alerts for these.
DBCC SQLPERF (LOGSPACE) GO
As a result we have Database name, total log file size as well as the filled log space percentage. Last column status is always zero.
Here are some handy and useful scripts that may be used to analyze the report usage of your SSRS report server. All these are select scripts and are using NOLOCK hint. So feel free to add filters and customize according to your specific requirements.
To get the last Time a Report was generated we have following script. Further filters may be applied if required.
SELECT DISTINCT C.NAME AS [Report Name] ,MAX(EL.TIMESTART) AS [LAST START TIME] FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) EL INNER JOIN REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID = C.ITEMID GROUP BY C.NAME ORDER BY C.NAME
If you want to analyze that how many reports were viewed on a specific day then we have following script. It generates report usage Stats by Date. It is advisable to insert the name of the account that runs SSRS. The reason is that if you have a report that is run off of cache or has subscription, the counts will show here. So if you want to see reports that have been run by users only then filter out the account that runs the SSRS service.
SELECT CONVERT(VARCHAR(25), TIMESTART, 101) AS [SPECIFIC DATE] ,COUNT(*) AS [Gneerated Number] FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) --WHERE USERNAME NOT IN GROUP BY CONVERT(VARCHAR(25), TIMESTART, 101) ORDER BY CONVERT(VARCHAR(25), TIMESTART, 101) DESC
Get the report server usage during the hours.
SELECT DATEPART(HOUR, TIMESTART) AS HOUR ,COUNT(*) AS [Gneerated Number] FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) --WHERE USERNAME NOT IN GROUP BY DATEPART(HOUR, TIMESTART) ORDER BY DATEPART(HOUR, TIMESTART)
To get the reports generated by users we have following script.
SELECT EL.USERNAME ,C.NAME ,COUNT(1) AS [Gneerated Number] FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) EL INNER JOIN REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID = C.ITEMID GROUP BY EL.USERNAME ,C.NAME ORDER BY EL.USERNAME ,C.NAME
We all are involved with Microsoft products and MS has shown wonderful improvement in all its products. Obviously a very dedicated teamwork is behind all this improvement. And what if you get a chance to learn from those skillful and handwork resources who are part of products success. It will be a great deal of course. So here is a link that will lead you to lot of quality and focused videos on various Microsoft products and technologies. Watch online or download the short videos.
If you are new to DBA career or did not spend enough time on SQL Server Query Tuning skills then here is a right stuff to start with. As licensing models are changing and adding hardware also associates the licensing costs along with the hardware cost itself so fine tuning the queries and procedures is a required skill for DBAs.
You can download about 6 hours of training videos related to SQL Server Query Tuning skills. Videos are equipped with theory and demos side by side. These training videos are prepared for SQL Sentry by