Hi all,
I'm running SQL 7.0. Due to large tables, the space on the SQL server was getting filled up. So i truncated some tables. Now even though the SQL dbase shows 3Gb free, the HDD shows only 150 MB free. How do i get SQL 7.0 to release the sapce back to HDD?
Thanks
MKB
SQL7: After doing a full backup Enterprise
Admin still shows "Transaction log space"
in use. Isn't it that way that full backup
releases all space formerly used by
log files?
Greetings from Mannheim, Germany
Ricardo
Hi,
When -all- records from a table with a varbinary(max) column are deleted (not via truncate), the table properties
still show a dataspace size from before the delete operation.
Inserting new blob records only leads in the growth of the allocated space withouth reusing the empty
already allocated space.
Runnning commands like dbcc updatusage/checkdb/cleantable/reindex or sp_spaceused @updateusage = N'TRUE'
seem to have no effect.
Does anyone know when space allocated by a varbinary col. is released?
thanks,
Derk
running SQL 2005 STD ed SP2. DB is in simple recovery mode.
Hi,
I am using SQL2005 SP1 and I have a 4Gb Tempdb (datafile) with virtually nothing in it.
I am unable to release the free space to the operating system. I have used dbcc shrinkfile...truncateonly but this has had no effect.
There is no error message and there are no open transactions, I have attempted to drop the data file by transferring to a new file but I can't because it the primary file...
Any idea would be very helpful!!
Cheers.
Hey guys,
I have a table which has 6 text columns (tblA).. I no longer require 1 of those text columns and want to reclaim the space that it is currently taking up..
Is the only way to BCP out all the data (except the 1 column i no longer require) drop the column and BCP the data back into the table?
I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.
thx
OS: Windows Server 2008 R2 Standard
SQL Server: 2008 SP1 Standard
We have a database with about 500 GB of free disk space on data file and the database is being set to read only mode for the fore-see-able future. We would like to release this unused disk space. We know that we could shrink the data file and then work on re-indexing to remove fragmentation.we wanted to check if some other method like backup and restore of the database could free up unused disk space in the data file. if someone was able to release unused disk space on data file by implementing a backup and restore.
Hi ,
I have a table let say TableA , which have a size of 22 GB.Due to its size i down size this table by deleting 40% rows.So ideally it should size to 15 GB but space is not released by TableA. How i can do it ? I tried shrink database, shrink data file wizards and DBCC command , Reorganize the index but all is in vain.
Any suggestion /solution?
I have scenario where I have process that loades data into SQL server 2012 database by doing some manipulation on data like sorting , aggregation, etc. Once this process is completed it's not free up the Tempdb space. If I restart the database, then it does.
is there any way (apart from shirking) to release space for Tempdb, like writing some post SQL queries to delete/ truncate the data and logs from temp db?
Hi,
I did a same server upgrade from SQL 6.5 to 7.0 about a month ago. I never deleted the old 6.5 installation/databases. Yesterday, for some reason SQL 7.0 wouldn't start. When I looked at SQL 6.5 enterprise manager, it showed that 6.5 was started. So I stopped it. I tried to start 7.0 back up and it would just start 6.5. I tried a reboot... that didn't work. Then I uninstalled 6.5, rebooted, and SQL 7.0 finally came up. I check the error logs and it showed that there was a "short stack dump" (details below)
Would a "stack dump" have caused sql to revert back to 6.5?
Thanks,
LC
Dump thread - spid = 10, PSS = 0x3505b4b4, EC = 0x3505b664
Stack Dump being sent to e:MSSQL7logSQL00003.dmp
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 03/13/00 15:07:33 spid 10
*
* Input Buffer 66 bytes -
* SELECT category FROM z_guarantees WHERE z_guarantees.code ='GGTD'
*
************************************************** *****************************
-------------------------------------------------------------------------------
Short Stack Dump
0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b)
0x0077fc6b Module(sqlservr+37fc6b) (utassert_fail+1a0)
0x005b0ebe Module(sqlservr+1b0ebe) (XCB::Rollback+4a)
0x005aa843 Module(sqlservr+1aa843) (bds_defect_xact+84)
0x0051abea Module(sqlservr+11abea) (clean_process+200)
0x005b22fd Module(sqlservr+1b22fd) (language_exec+5f9)
0x4106135c Module(opends60+135c) (execute_event+659)
0x4106164b Module(opends60+164b) (process_commands+f3)
0x4109285a Module(ums+285a) (ProcessWorkRequests+ed)
0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139)
0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce)
0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be)
2000-03-13 15:07:34.09 spid10 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000)
2000-03-13 15:07:34.16 kernel SQL Server Assertion: File: <xcbmgr.cpp>, line=1299
Failed Assertion = 'pss->IsXcbLocked ()'.
Dump thread - spid = 10, PSS = 0x3505b4b4, EC = 0x3505b664
Stack Dump being sent to e:MSSQL7logSQL00004.dmp
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 03/13/00 15:07:34 spid 10
*
* Input Buffer 66 bytes -
* SELECT category FROM z_guarantees WHERE z_guarantees.code ='GGTD'
*
************************************************** *****************************
-------------------------------------------------------------------------------
Short Stack Dump
0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b)
0x005b5af3 Module(sqlservr+1b5af3) (ex_raise2+35c)
0x00506417 Module(sqlservr+106417) (ex_raise+5f)
0x0077fbfe Module(sqlservr+37fbfe) (utassert_fail+37e)
0x005b0ebe Module(sqlservr+1b0ebe) (XCB::Rollback+4a)
0x005aa843 Module(sqlservr+1aa843) (bds_defect_xact+84)
0x0051abea Module(sqlservr+11abea) (clean_process+200)
0x005b22fd Module(sqlservr+1b22fd) (language_exec+5f9)
0x4106135c Module(opends60+135c) (execute_event+659)
0x4106164b Module(opends60+164b) (process_commands+f3)
0x4109285a Module(ums+285a) (ProcessWorkRequests+ed)
0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139)
0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce)
0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be)
2000-03-13 15:07:34.22 spid10 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000)
2000-03-13 15:07:34.44 spid1 Closing file e:mssql7datarain.mdf.
2000-03-13 15:07:34.83 spid1 Closing file e:mssql7datarainlog.ldf.
Dump thread - spid = 8, PSS = 0x2110b4b4, EC = 0x2110b664
Stack Dump being sent to e:MSSQL7logSQL00005.dmp
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 03/13/00 15:07:34 spid 8
*
* Input Buffer 62 bytes -
* select interface from z_property where ( property ='DALMAN' )
*
************************************************** *****************************
-------------------------------------------------------------------------------
Short Stack Dump
0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b)
0x0077fc6b Module(sqlservr+37fc6b) (utassert_fail+1a0)
0x005b0ebe Module(sqlservr+1b0ebe) (XCB::Rollback+4a)
0x005aa843 Module(sqlservr+1aa843) (bds_defect_xact+84)
0x0051abea Module(sqlservr+11abea) (clean_process+200)
0x005b22fd Module(sqlservr+1b22fd) (language_exec+5f9)
0x4106135c Module(opends60+135c) (execute_event+659)
0x4106164b Module(opends60+164b) (process_commands+f3)
0x4109285a Module(ums+285a) (ProcessWorkRequests+ed)
0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139)
0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce)
0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be)
2000-03-13 15:07:34.92 spid8 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000)
2000-03-13 15:07:35.00 kernel SQL Server Assertion: File: <xcbmgr.cpp>, line=1299
Failed Assertion = 'pss->IsXcbLocked ()'.
2000-03-13 15:07:35.06 spid8 Using 'sqlimage.dll' version '4.0.5'
I want to use RAID 1 for System database with sQL Server 2005 64 bit.
Not including TEMPDB.
Is there any point(s) or fact that i should be aware of before I decide on space requirements for system databases(not including TEMPDB). and what should be recommended space keeping in mind the future growth for system databases.
or any factor(s) that can trigger size increase in them.....!
Many Thanks,
Hi, all experts here,
Thank you very much for your kind attention.
I am wondering if we could back up the databases to any place outside of the local server system? As I found, we can only back up the database to the local server system, so we have needs to share databases on network places. Is there any method to back up the database on network place rather than first of all I have to back up the database on a local server system, then copy it to the network place, that just sounds really inconvenient.
Thanks a lot in advance for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi all
Iam working in Prodcution ENV,Please help how make space
The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.
Hi Guys,
Can anyone help me with this error
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue
was full.
I have three packages running in parallel. This package download data from different databases from ServerA into ServerB.
At some point one of the package is getting the error above at random time.
Hi Guys,
Can anyone help me with this error
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue
was full.
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies View RelatedNew to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies View RelatedHello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
View 2 Replies View RelatedHi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedI have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
Hello,
Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?
3700 MB allocated but only 100 MB used and 3600 MB are free !
Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)
Thank you for your precious help !
Khaix from Brussel.
How do we suppress multiple spaces to a single space in T-SQL
E.G.
Field: FullName
e.g.
WOMENS HEALTH RIVER VALLEY
JOHN FAMILY MED GROUP
HERSH STWEART P.
PARK HEIGHTS MEDICAL CENTER
KOPP WHITEFIELD E
The o/p wanted is
HERSH STWEART P.
Thank you.
Hello,
I've tried searching for details on the next release date of SSRS, but can't find any. Has anyone heard anything regarding the next release? There are at least a dozen features that I really would like to have, and when searching for each, the MSFT developer typically responds with "that feature is targeted for a future release". So I'd like to know if anyone has any idea, even heard a rumor, regarding the next release timeline. Or, is there a better place to search for this information?
Thanks!
Michael
anyone have any ideas on when beta 3 will be available?
View 3 Replies View RelatedCheck this out
this is the replacement for MSDE 2000
SQL Server 2005 Express Edition (http://lab.msdn.microsoft.com/express/sql/default.aspx)
Top 10 cool things about SQL Server Express Edition (http://lab.msdn.microsoft.com/express/sql/top10/default.aspx)
SQL Server Express Books Online (http://www.microsoft.com/downloads/details.aspx?FamilyId=2ADBC1A8-AE5C-497D-B584-EAB6719300CD&displaylang=en)
by the way if you follow the first link, you will find a pretty accurate photo of rdjabarov...
If we upgrade an instance to SP2 (CTP), can we simply apply SP2 (Release) on top of the SP2 (CTP)? Or will we need to uninstall the whole instance, re-install SQL 2005 and only then apply SP2 (Release)?
I notice in section "1.1 Overview of SQL Server 2005 SP2 Installation" of the documentation the statement:
"During installation, SQL Server 2005 SP2 Setup will list all installed components of SQL Server 2005 and allow you to select the components to upgrade, including components that have already been upgraded to SP2. For more information, see the SP2 Setup documentation."
I'm hoping that this means you can apply the release SP2 on top of a ctp SP2 with the result being full release SP2 versions. I've read the "SP2 Setup documentaiton" and it did not clarify for me.
Thanks!
Bob Hodgman
P.S. - sorry for the duplicate posting... I think posting this as a reply to the announcement (as I originally did) didn't get it much attention.
I can't find any timeframe for the 1.0 release of SQL Server Everywhere. Is this information available?
View 6 Replies View RelatedDear all,
We are working on C++ in eVC++ 3.0 environment (CE 3.0) with SQL CE 2.0.
While executing the SQL CE commands like GetData(),GetNextRows() and Seek(), 8 KB keeps increasing each time in the memory. We are releasing the accessors,columnsinfo,rowsetchange,rowset handles properly.But still the memory increases without being released.
Have we missed out anything?
Kindly help us in this regard.
Regards,
Sasi.