Posted on Leave a comment

Database Suspect Mode

1. Run a checkdb

DBCC CHECKDB ([db_name_here]) WITH ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS

2. Ensure there is enough free disk space for the data files and transaction log files to grow.

3. Check SQL Server and Windows event logs to see if can determine when corruption started, any errors or events that may have contributed to corruption, when last restart of instance, last online of database, I/O interruptions, power outages, etc. If “under the gun” to get db back online, this step may have to be performed post-mortem.

4. Attempt repair

EXEC sp_resetstatus 'db_name_here';
 ALTER DATABASE [db_name_here] SET EMERGENCY
 DBCC CHECKDB('db_name_here')
 ALTER DATABASE [db_name_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DBCC CHECKDB('db_name_here', REPAIR_REBUILD)
 ALTER DATABASE [db_name_here] SET MULTI_USER

5. If REPAIR_REBUILD does not fix it and data loss is a concern, then make copies of the *.ldf, *.mdf, and *.ndf files.

You can then attempt to restore the database from last good backups and transaction log backups up to the point of corruption, or attempt a REPAIR_ALLOW_DATA_LOSS

EXEC sp_resetstatus 'db_name_here';
 ALTER DATABASE [db_name_here] SET EMERGENCY
 DBCC CHECKDB('db_name_here')
 ALTER DATABASE [db_name_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 BACKUP DATABASE [db_name_here] TO DISK = N'R:\backup_path_here\db_name_here.bak'
 DBCC CHECKDB('db_name_here', REPAIR_ALLOW_DATA_LOSS)
 ALTER DATABASE [db_name_here] SET MULTI_USER

6. If possible, can try restarting instance. If not possible to restart instance due to causing other DB outages, then attempt to offline database, then set back online. Sometimes suspect mode is due to a problem during the on-lining of the db and a second attempt to online db clears up suspect issue. Of course, in extreme cases, attempting this may result in the database not being able to online at all…

7. Once you do get it back online run a checkdb and a full backup

DBCC CHECKDB ([db_name_here]) WITH ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS

 BACKUP DATABASE [db_name_here] TO DISK = N'R:\backup_path_here\db_name_here.bak'

NOTE: See the suspect database a lot on EXPRESS editions and databases with AUTO_CLOSE enabled. If this is the case then sometimes the database does not “open” properly and reports as suspect. You can clear the suspect by offline then online the database. When it is back online, recommend turning the AUTO_CLOSE off.

Posted on Leave a comment

Powershell: Generate DDL for list of database objects


#******************************************************************
# POWERSCRIPT FILE NAME: getddl.ps1
# Scripts out DDL for given list of database objects
#******************************************************************
set-strictmode -version Latest;
#set-strictmode -off;

######USER PROVIDED VALUES#######
$SERVER_NAME = "MYSERVERNAME\MYINST";             #INSTANCE NAME
$DATABASE_NAME = "master";                        #DATABASE NAME
$OBJ_LIST = ('sysobjects','sp_who');              #PROVIDE LIST OF OBJECT NAMES IN ARRAY, EXAMPLE
#$OBJ_LIST = Get-Content "C:\temp\objects.txt";   #PROVIDE LIST OF OBJECT NAMES IN TEXT FILE, ONE ENTRY PER LINE
$OUT_PATH = "C:\temp";                            #OUTPUT FOLDER, MUST ALREADY EXIST


##GLOBAL VARIABLES##
$C_OK = 0;    # 0 for OK exit status
$C_NOTOK = 1; # Non-Zero for NOT-OK exit status


function main()
{
   $retstat = $C_OK;

   $tstamp = timestamp;
   write-host "$tstamp starting up";

   #connection string
   $CONN_STRING = "Server=$SERVER_NAME;Database=$DATABASE_NAME;Trusted_Connection=True";

   #initialize connection objects
   $SqlConn = New-Object System.Data.SQLClient.SQLConnection;
   $SqlConn.ConnectionString = $CONN_STRING;
   $SqlCmd = New-Object System.Data.SQLClient.SQLCommand;
   $SqlCmd.Connection = $SqlConn;

   #define command
   $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure;
   $SqlCmd.CommandText = 'sp_helptext';

   #define parameters
   $SqlCmd.Parameters.Add("@objname",[system.data.SqlDbType]::NVarChar) | out-Null;
   $SqlCmd.Parameters['@objname'].Direction = [system.data.ParameterDirection]::Input;

    
   try
   {
      $tstamp = timestamp;
      write-host "$tstamp connecting to $CONN_STRING";
      $SqlConn.Open();

      $tstamp = timestamp;
      write-host "$tstamp processing records";
      $n = 0;

      foreach ($obj in $OBJ_LIST)
      {
         $SqlCmd.Parameters['@objname'].value = clnStr $obj;
           
         #cmdToString $SqlCmd; ##output SQL command 
         $rs = $SqlCmd.ExecuteReader();

         $outfile = "$OUT_PATH\$obj.sql";

         $tstamp = timestamp;
         write-host "$tstamp Collecting $obj";

         $s = '';
         while ($rs.Read())
         {
            $s += $rs.GetValue(0);
         }
         $rs.Close();
         $rs.Dispose();

         $tstamp = timestamp;
         write-host "$tstamp Writing $outfile";
         $sw = new-object system.IO.StreamWriter($outfile);
         $sw.write($s);
         $sw.close();

         $n++;
      }#foreach
    
      $SqlCmd.Dispose();
      $SqlConn.Close();
      $SqlConn.Dispose();    

      $tstamp = timestamp;
      write-host "$tstamp processed $n objects";
   }
   catch
   {
      $line = $_.InvocationInfo.ScriptLineNumber
      $e = $_.Exception
      Write-Host -ForegroundColor Red "Exception: $e at line $line"
      #echo $_.Exception|format-list -force;  ##verbose error message
      $retstat = $C_NOTOK;
      cmdToString $SqlCmd;
   }
   
   $tstamp = timestamp;
   write-host "$tstamp shutting down";    
   exit $retstat;
}#main()


function timestamp()
{
   return($(get-date -F "yyyyMMdd_HHmmss"));
}#timestamp()



function clnStr( $val )
{
   if ( $($val) -eq $null )
   {
      $ret = [System.DBNull]::Value;
   }
   else
   {
      $ret = $val.toString().replace("'","''");
   }
   return($ret);
}#clnStr()



function cmdToString($cmd)
{
   $c = " ";
   $s = "EXEC " + $cmd.CommandText;
   foreach ($p in $cmd.Parameters)
   {
      $s += $c + $p.ParameterName;
      if ( $p.Value.Equals([DBNull]::Value) )
      {
         $s += " = NULL";
      }
      else
      {
         $s += " = '" + $p.Value + "'";
      }
      $c = ", ";
   }
   write-host $s;
}#cmdToString()


clear;
main;

Posted on Leave a comment

Matching SQL Agent Jobs to SSRS Subscriptions

Run this query on the instance hosting your SSRS databases to get list of SSRS subscriptions and their corresponding SQL Agent job names.


   SELECT b.[name] "JobName"
        , e.[name] "ReportName"
        , e.[path] "ReportPath"
        , d.[description] "SubscriptionDescription"
        , a.SubscriptionID
        , laststatus
        , eventtype
        , LastRunTime
        , date_created
        , date_modified
     FROM ReportServer.dbo.ReportSchedule a 
     JOIN msdb.dbo.sysjobs b
       ON cast(a.ScheduleID as varchar(1000)) = b.[name]
     JOIN ReportServer.dbo.ReportSchedule c
       ON a.ScheduleID = c.ScheduleID
     JOIN ReportServer.dbo.Subscriptions d
       ON c.SubscriptionID = d.SubscriptionID
     JOIN ReportServer.dbo.[Catalog] e
       ON d.report_oid = e.itemid

This can be useful if you need to explicitly execute the subscription directly from SQL or need to customize the SQL Agent job in some way such as preventing the report from being run based on conditions that cannot be defined in SSRS subscription manager.

Posted on Leave a comment

Sharepoint and Auto-Create statistics

Sharepoint has built-in statistics that are optimized for how Sharepoint works.

For SharePoint 2010 it is not recommended to Auto-Create statistics for any databases.  Auto-Update statistics can be enabled for content databases if you are not manually updating statistics and you are experiencing performance issues.

For SharePoint 2013, enabling Auto-Create Statistics and Auto-Update Statistics is NOT supported.

https://docs.microsoft.com/en-us/SharePoint/administration/best-practices-for-sql-server-in-a-sharepoint-server-farm

Posted on Leave a comment

Reset Windows Stored Credentials

If you need to reset your saved your credentials to access the share folder from your PC you can delete those credentials by opening a command prompt running as administrator.

Then type the command:

rundll32.exe keymgr.dll, KRShowKeyMgr

You can then delete the stored credentials used to authenticate the share folder path on your local pc.

You can then try to access that share folder path and it should prompt you for the account user name and password again.

Posted on Leave a comment

SQL Server 2008R2 Integration Services Access

Problem: SSIS Support Developer could not access SSIS packages in SQL Server 2008R2 Instance.

Solution:

  1. Type in “Component Services” in search bar
  2. Open Component Services
  3. Expand Component Services -> Computers -> My Computer -> DCOM Config
  4. Scroll down to MsDtsserver100 and right-click Properties

Select the Security tab

Click edit button for Launch and Activation Permissions and add permissions, same for Access Permissions

Click OK

 

Next, Right-click My Computer>Manage>Configuration>Local Users & Groups

Look for the Distributed COM Users group and add the account.

 

 

Finally, restart the SSIS service

Posted on Leave a comment

Using LogParser to get list of Sharepoint Usernames

Download and install LogParser from here:

https://www.microsoft.com/en-us/download/details.aspx?id=24659

Then create a text file query.sql containing your query


   SELECT distinct 
          replace_str(replace_str(to_lowercase(cs-username),'0#.f|ldapmember|',''),'0#.w|','') as username
     FROM C:\inetpub\logs\LogFiles\*.log
    WHERE cs-username > ''
      AND date >= '2017-01-01'

 

Then execute the following command

C:\Program Files (x86)\Log Parser 2.2\logparser file:"query.sql" -i:IISW3C -o:CSV -recurse > c:\temp\usernames.csv

(note: path to LogParser may vary on your system, it may also be handy to add it to the %PATH%)

Learn more about LogParser here:
https://technet.microsoft.com/en-us/scriptcenter/dd919274.aspx

Posted on Leave a comment

A call to ‘LogonUserW’ failed with error code: ‘1385’

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to ‘LogonUserW’ failed with error code: ‘1385’.

1385 = Logon failure: the user has not been granted the requested logon type at this computer.

Solution:

  1. Look up the Windows user that is assigned to credential [##xp_cmdshell_proxy_account##]
  2. Run secpol.msc
  3. Navigate to “Local Policies” -> “User Rights Assignment” -> “Log on as a batch job”
  4. Add the user found from step 1 to “Log on as a batch job”
  5. Test by executing sql stored procedure: “EXEC master..xp_cmdshell ‘whoami'”

 

Troubleshooting xp_cmdshell failures

Posted on Leave a comment

Instant PRINTs

SQL “PRINT” statements getting buffered and not displaying until buffer is flushed with batch is done or gets full.

Using a RAISERROR with severity of 0 and “WITH NOWAIT” will not interrupt the batch, but will immediately display the output.

Here’s an example keeping it to one line and including a timestamp…

–for first msg in batch
DECLARE @msg nvarchar(2044) = convert(varchar(20),current_timestamp,120) + ‘ – Your First Message Here’; RAISERROR(@msg, 0, 1) WITH NOWAIT;

–for rest of msgs in batch
SET @msg = convert(varchar(20),current_timestamp,120) + ‘ – Subsequent Messages Here’; RAISERROR(@msg, 0, 1) WITH NOWAIT;

GO

Posted on Leave a comment

SSRS Temp files filling drive

If you have SSRS files filling your temp drive, look for the RSTempFiles.

Default folder:

C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesRSTempFiles

You can move this folder to a different drive by copying the RSTempFiles folder to the target drive (for this example “R:RSTempFiles”).

Make sure that the SSRS service account alias has full permissions on the folder

Default local machine service account alias:

[NT SERVICEReportServer]

Locate the rsreportserver.config file

Default folder:
C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesReportServer

In this file Located the <Service> tag and add entry for FileShareStorageLocation with new path:

<Service>
  <FileShareStorageLocation>
    <Path>R:RSTempFiles</Path>
  </FileShareStorageLocation>


</Service>

After saving the file restart the SSRS instance.  It should start using the new location.

For more info about the files stored in this folder:


https://blogs.msdn.microsoft.com/jgalla/2008/06/30/all-those-temporary-files-rstempfiles/