.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Shrink Log In SSMS

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Hello all,

I have tried to shrink a log file with SSMS with releasing all unused space, but the attempts failed twice. Any solution?


View Complete Post

More Related Resource Links

Simultaneously connecting to db with VS, website and SSMS


I have recently combined my aspnet membership database and main website database into one database file, I'm happy with the schemas, stored procedures etc. (This is a development project not yet a production project.)

My problem is that I can no longer simultaneously connect to the database from Visual Studio if a user is logged in to the website.



Scenario 1: No user is logged in to the website. I can go to the Data Connection window in VS, choose "Add Connection" and then inspect the contents and schemas of the database. This is fine.

Scenario 2: A user is logged in on the website. I try to connect to the website in VS and get the error:

"Cannot open user default database. Login failed. "

Scenario 3: I am connected to the database in VS and nobody is logged into the website. If someone tries to log into the website they get the following error:

Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

So it seems from the above that there can be only one connection to the database at a time? However this was not the case before I combined the membership database with the main website database. I could always make changes as a user logged

Copy/paste broken in SSMS 2008 R2

After uninstalling an SSMS addin (from Red Gate), my copy/paste functionality in SSMS broke -- don't know if it's correlated.  As documented in the bug https://connect.microsoft.com/SQLServer/feedback/details/126743/copy-paste-stop-working-in-sql-2005 it seems that paste is working fine, because it works when I copy from other programs.  When I paste from SSMS, though, I can't paste anywhere.  It's as though SSMS clears the clipboard and that's all she wrote. Anyone else seeing this problem? -matthew

SQL 2008 R2 (final): SSMS freezes: "Failed to retrieve data for this request.

The original thread is locked for some reason--but the cause of the bug had something to do with the "ssms tools pack" add-in, which, according to the comments in the connect item, will be fixed in an update of ssms tools: http://social.msdn.microsoft.com/Forums/en-US/sqlkjdatabaseengine/thread/f65737b7-fe4d-487b-aa41-ef437d100bc7 https://connect.microsoft.com/SQLServer/feedback/details/556169/ssms-completely-locked-up-after-right-clicking-a-table  

Export Maintenance Plan in SSMS

Where are the subplan schedules when I exported /imported a Maintenance Plan in SSMS ? How can I ensure that they are exported with ?

SSMS Project templates

I am using SQL2008R2. From SSMS when I try to create a new project three template choices are shown. SQL Server Scripts, Analysis Services Scripts and SQL Server Compact Edition Scripts. Regardless of which one I pick the solution explorer window is empty and there is no apparent way to add anything to it? I suspect the templates are messed up. How can I replace what is there with ones that work? Thanks.Tom

SSMS on networked machine - can see server but not instance

Hi, We have been running sql server 2008 enterprise on a single networked machine (SBS 2008) which is also runs DHCP and DNS.  We have bought a new server to just run SQL Server 2008 and I cannot manage to connect to it via SSMS from my laptop - I can see the server but not the instance.   Here is roughly what I have done: Installed Windows Server 2008 Enterprise on the box Installed SQL Server 2008 Enterprise on the box Create a named instance Configured instance to listen on TCP 1533 Created a new firewall rule to allow traffic through on TCP 1533 Created a new rule for sqlsrvr.exe on the firewall I have the services loging on with the following accounts: SQL Server Integration Services 10.0 = DOMAIN\dbadmin = running = automatic SQL Server Analysis Services = DOMAIN\dbadmin = running = automatic SQL Server (namedinstance) = DOMAIN\dbadmin = running = automatic SQL Full-text Filter Daemon Launcher = NT AUTHORITY\LocalService = running = manual SQL Server Reporting Services = NT AUTHORITY\LocalService = running = automatic - this is installed but not configured SQL Server Agent = DOMAIN\dbadmin = Stopped = manual SQL Server Browser = LocalSystem = running = automatic When I log onto the new box as a domain user and log into management studio on my laptop I can then suddenly see the instance and connect. It sounds to me that it is a permissions / acc

How can I access Command Window in SSMS 2008

Hello, I'm debugging some t-sql code, I can view locals, watches etc. but I've closed command window by mistake and i couldn't find it again. Is there a shortcut key for this window? How I can access this window? Thanks in advance.

how do I add a custom report that appears in SSMS

Hi, I would like to create and add a custom report that would appear when users click on Reports - Custom Reports at both the Instance and the DB level in SSMS. I know how to create a report but I dont know how to add the report so it appears when users click through SSMS. I would also like to know where is the RDL (if that is what is being used) for the build in reports for either SQL Server 2008 and/SQL Server 2008 R2. thanks, SQLSearcher

SSMS 2008 - Shows Service Status as Stopped Though Service is started

Hi, I have a SQL Server 2008 - Active/Passive Cluster. The issue I am facing here is that, when I do a Failover from Node A to Node B, All the Cluster Services come online and the SQL Services are running (checked using the SQL Configuration Manager and Services console). However when I open the management studio, The Service status Indicator shows a Red Block mark against the ServerName and the SQL Server Agent. When I try connecting to the server remotely from another server, the status is displayed correctly in the SSMS(i.e the green sign with the arrow) .  This issue happens when I locally login to the server and connect using ssms. I am an Admin on both the boxes and even the service account with which the services run also has admin privileges. Any pointers? Thanks,

MDX works fine in SSMS but not in query designer

Hi all,   I am trying to include this query in my query designer in SSRS. The query below is as follows:WITH MEMBER [Time].[Fiscal Month].[All].[ Subtotal] AS ' AGGREGATE( EXISTING { [Time].[Fiscal Month].[All].CHILDREN }) ', SOLVE_ORDER = 1000  SELECT NON EMPTY { { { { [Time].[Fiscal Month].[All].CHILDREN }, { [Time].[Fiscal Month].[All].[ Subtotal] } } * { { [Measures].[SKU Distinct Count] } } } } ON COLUMNS , NON EMPTY { [Sales Force].[Retailer Code].[All].CHILDREN } ON ROWS  FROM [Sales]CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL The error i am getting isThe query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..Please let me know where am i going wrong.Thanks and regards,ram

Can connect via SSMS but not SQLCMD/OSQL

I can connect to my SQL 2008 Express Edition server using SSMS.  I cannot connect using OSQL or SQLCMD and my application is having sporadic connection issues.  When I attempt to connect via OSQL, the following error is returned: Error: 18452, Severity: 14, State: 1.  Login failed.  The login is from an untrusted domain and cannot be used with windows authentication. The following are errors present in the SQL errorlog:  Error: 17806, Severity: 20, State: 2.  SSPI handshake failed with error code 0x8009030c while establishing a connection iwth integrated security; the connection has been closed. [CLIENT: <local machine>] Error: 18452, Severity: 14, State: 1.  Login failed.  The login is from an untrusted domain and cannot be used with windows authentication. I see an error in the application event log: Event 6037, LsaSrv  The program sqlservr.exe, with the assigned process ID 1788, could not authenticate locally by using the target name MSSQLSvc/{srvnm}.{workgrp}.net:{SQLinstancenm}.  The target name used is not valid.  A target anme should refer to one of the local computer names, for example, the DNS host name.  Try a different target name. Has anyone else experienced this issue - what else can I look into?      

SSMS 2008 & Activity Monitor's Processor Time graph is disabled

Hi,I just upgraded one of my SQL 2005 clusters to SQL 2008 with CU9.  Now, when I connect to it with SSMS, either from my workstation or directly on a cluster node, the % Processor Time graph in the Activity Monitor screen is disabled.  The other three graphs are fine.  If I connect to a different SQL 2008 server, the % Processor Graph is fine.  I don't see any errors in any app event log.  Has anyone else seen this behavior?Thanks!

When to add "tcp:" to "Server name:" connecting from SSMS remotely

Having read in [1] that to connect to the database engine from another computer using SSMS "In the Server name box, type tcp: to specify the protocol, followed by the computer name... type tcp:<computer_name>. In our example for a named instance, type tcp:<computer_name>,49172", why do I connect to remote server without adding "tcp:"? I do not remember adding "tcp:" connecting to earlier versions of MS SQL server either... [1] Connecting to the Database Engine from Another Computer(MS SQL Server 2008 R2) http://msdn.microsoft.com/en-us/library/ms345343.aspx#otherComp

Shrink database log file sql server 2005

Hi, I am trying to shrink log file for a testing database.This database will be restored with production database periodically.When i tried to create backup of the log file Processed 0 pages for database 'hcbeta', file 'HC_log' on file 6. The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. BACKUP LOG successfully processed 0 pages in 0.423 seconds (0.000 MB/sec). I tried EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 Resulted with error:Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. So tried to publish the database using EXEC sp_dboption 'hcbeta', 'Publish', 'true' resulted error mesage:Msg 15242, Level 16, State 1, Procedure sp_dboption, Line 138 Database option 'Publish' is not unique. and retrived duplicate_options as "merge Publish" ,"Publish" Plesae help me in shrinking my database log file. Thanks, Adi.

SSMS 2008 Activity Monitor Activity Monitor is unable to execute queries against server (SQL Server

I get the following error when running SQL Server SSMS 2008 (32-bit) on my (Windows 7 64-bit) workstation against our SQL Server 2005 (32-bit) development server running Windows Server 2003 (32-bit): TITLE: Microsoft SQL Server Management Studio ------------------------------ The Activity Monitor is unable to execute queries against server <my server\instance name>. Activity Monitor for this instance will be placed into a paused state. Use the context menu in the overview pane to resume the Activity Monitor. ------------------------------ ADDITIONAL INFORMATION: Unable to find SQL Server process ID 2256 on server VTX-DEV-01 (Microsoft.SqlServer.Management.ResourceMonitoring)   I can expand the panes and all display data except Overview and Data File I/O. This is true for all instances on that server. The SSMS 2005 Activity Monitor (also on my workstation) works fine when connected to that server. I checked for the registry setting to disable the performance counters on both my machine and the server, and it was not there. I started the Performance Counter DLL Host on my machine, but that made no difference. I can see all Processor counters on the server from my local Performance Monitor. I ran "lodctr /R" on both systems, but that didn't help. The only SQL counters I see on the server (from my workstation) are SQLServer:SSIS Pipeline and SQLServer:SSIS

How to get SSMS SCRIPT AS ... output programmatically using VB.NET?

I'm looking for a way to get the output from a "SCRIPT AS ... CREATE TO ... New Query Window" under SSMS, but using VB.NET (or C#) code to initiate the output which I can then save off or use internally in my .NET application.Basic idea is that:1.  Connect to a master server2.  Initiate SCRIPT AS ... CREATE TO ... on an existing table3.  Use the scripted TSQL in my application to create that table on another SQL instance/serverExample of scripted output that I want to use in my .NET application   USE [MyTable] GO /****** Object: Table [dbo].[MyTable] Script Date: 03/09/2010 08:24:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MyTable]( [MyField1] [bigint] IDENTITY(1,1) NOT NULL, [MyField2] [nvarchar](5) NOT NULL, [MyField3] [bigint] NOT NULL, ... I've looked at sp_helptext, but I don't think that is what I need, not sure.Thanks, Rob.

Why doesn't SQL Server 2005 Enterprise always shrink a log file after a backup?

I have several databases that, despite being backed up regularly, do not release space to the operating system.  In one extreme case, I have a log file that is less than 1% utilized (and has been for months.) I know how to manually force a shrink, but I should not need to do that on any sort of regular basis.  In fact, it is against best practices to manually shrink a log file (or any database file for that matter) because of the very high risk of an unrecoverable error. It is my understanding that SQL Server will eventually handle these things on its own.  However, it does not appear to be doing so.  Any thoughts as to why? Thanks, Dave
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend