SQL Stuff

Published 08-26-2017 00:44:52

Shrink log files

  • Log into Microsoft SQL Server Management Studio
  • Right click on your database
  • Select Properties
  • Click on Options
  • Change the recovery model to Simple
  • Click OK
  • Right click on the database
  • Select Tasks > Shrink > Database
  • Click OK
  • Right click on the database
  • Select Properties
  • Click on Options
  • Change the recovery model to Full
  • Click OK

Reset sa password

From command prompt

osql -L

Copy full name of SQL Server and type:

OSQL -S <insert_servername_here> -E

Execute the following query:

sp_password NULL, '<insert_new_password_here>', 'sa'
GO

From SQL Management Studio

  • Open SSMS management console. This is usually under Start -> All Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio Express.
  • Connect to your instance of SQL Server via Windows Authentication.
  • Once open, navigate to Logins object under Security. Once expanded, right-click on the SA user, and choose Properties.
  • Enter desired password in front of Password and Confirm Password field and click OK.

Using SQL Password Recovery Tool

  • Run the SQL Password Recovery program and then click Open to load the master .mdf file.
  • Select SA user name to reset the forgotten SA password, or select any other user name which you forgot the password. Click Change Password to reset password.
  • Enter the new password and click OK.

When You Have SA Disabled

  • Start SQL server as single user using -m flag via command line.
sqlcmd -m
  • Run as Administrator
  • On the Server Roles page, select sysadmin and reset password.

Set RAM limits to internal SBS database

  • Open SQL Server Management Studio
  • Connect to server name
\.pipemssql$microsoft##sseesqlquery
  • Right click root node
  • Click Properties
  • Click Memory
  • Change the value of Maximum server memory (in MB)

Error: SQL Server does not exist or access denied.

  • Can’t telnet to port 1433, can to other ports
  • TCP/IP and named pipes enabled
  • Services properly authenticated and started/restarted
  • Firewall disabled or exceptions given to 1433 inbound

What tripped me up was that I was working with a non-default instance (original one was disabled). Apparently, the non-default instance gets a random port generated for it. Also, the port doesn’t seem to show itself anywhere in the SQL management tools.

The port was found here:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp\IPAll