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.
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
I was required to rerun some failed SSRS subscriptions. As we know that SSRS subscriptions automatically create scheduled jobs in SQL Server agent. The problem is that job names are not readable and it is some UID. So first of all i got the list of failed subscriptions from execution logs and then i was required to get job name for each failed subscription so that it may be executed manually. For this purpose i found following piece of code and it worked fine for me. In following example i got the job name for a report named MyReport.
SELECT b.NAME AS JobName,e.NAME ,e.path ,d.description ,a.SubscriptionID ,laststatus ,eventtype ,LastRunTime ,date_created ,date_modified FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b ON a.ScheduleID = b.NAME JOIN ReportServer.dbo.ReportSchedule c ON b.NAME = c.ScheduleID JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID JOIN ReportServer.dbo.CATALOG e ON d.report_oid = e.itemid WHERE e.NAME = 'MyReport' and b.description = 'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'
Above piece of code may be found in full context here.
If replication is implemented then it is important to have a list of objects that have been replicated. Replicated objects are known as article in terms of replication setup. So articles may have slightly different concerns in terms of performance, drop or truncate command. Use the following simple script to get the list of replicated objects in a database.
USE [Ur DB Name Here] GO SELECT NAME AS [Object Name] ,type_desc AS [Object Type] ,is_published AS [Is Data and Schema Published] ,is_schema_published AS [Is Only Schema Published] FROM sys.objects WHERE is_ms_shipped = 0 AND (is_published = 1 OR is_schema_published = 1)
[Is Data is Schema Published] is for tables. While [Is Only Schema Published] is for other objects like USPs, UDF and Views.
Suppose we have to create a procedure which would drop all the tables having a given naming convention. Procedure would utilize a cursor and dynamic SQL. Table names would be pulled from SYS.Objects catalog. Here is the commented procedure for demo
/* Demo would drop the required tables by using a cursor in following steps 1. Create tables for drop demo 2. Verify the created tables 3. Create USP 4. Create cursor in USP to pull and execute the drop commands 5. Execute the procedure 6. Verify that tables do not exist now */ -- Create Tables create table rep1 (id smallint) GO create table rep2 (id smallint) GO create table rep3 (id smallint) GO create table rep4 (id smallint) GO -- verify the created tables SELECT name FROM SYS.OBJECTS WHERE TYPE = 'U' and name like '%ConventionHere%' GO ------------------------------------------------------------- -- Create USP and a cursor in USP If exists (select name from sys.objects where type = 'P' and name = 'USP_DropRepTables') DROP PROCEDURE USP_DropRepTables GO CREATE PROCEDURE USP_DropRepTables AS -- Declare variable for SQL command DECLARE @dropQuery NVARCHAR(400) -- Declare and populate cursor with required tables DECLARE DropLoopCursor CURSOR FOR SELECT 'DROP Table '+name FROM SYS.OBJECTS WHERE TYPE = 'U' and name like '%ConventionHere%' -- Open cursor OPEN DropLoopCursor -- Fetch record from cursor FETCH NEXT FROM DropLoopCursor INTO @dropQuery -- Configure while loop in cursor WHILE (@@FETCH_STATUS <> -1) BEGIN -- Execute Dynamical SQL execute (@dropQuery) -- Fetch next recod FETCH NEXT FROM DropLoopCursor INTO @dropQuery END -- Close and deallocate the cursor CLOSE DropLoopCursor DEALLOCATE DropLoopCursor ------------------------------------------------------------ -- End the procedure GO -- Execute the procedure EXECUTE USP_DropRepTables GO -- Verify that tables do not exist now SELECT name FROM SYS.OBJECTS WHERE TYPE = 'U' and name like '%ConventionHere%' GO
You may copy and paste the whole code in SSMS query pane for a better view of comments. Also it is required to take care that given naming convention should not be used by any such table that is not required to drop.
I am required to generate a list of SQL Server scheduled jobs along with there associated steps. It would be compared to same list from another server where the jobs just have been transferred. Following code would provide me job names along with their steps
USE MSDB GO SELECT v.name AS JobName, step_name FROM sysjobs_view v inner join sysjobsteps t ON v.job_id = t.job_id ORDER BY v.name, step_name GO
We may add further properties of job or step from SYSJOBS_VIEW and SYSJOBSTEPS respectively.