HI all,
I have a database that allocated:
Data: 7300 MB
Log: 2000 MB
But only used
Data: 5500MB
Log: 50MB
How can I free the unused space in the transaction log because
the database is getting too big.
Thank you for your suggestion.
How can I determine how much unused space to remove from database files? I am setting up a maintenance plan and I am trying figure that out.
Also, is there a rule on how far to shrink TL?
Please help. Thanks.
Dear Experts,
I'm working for an ERP company, for our product, there are around 2000 tables. in each table, we are using 10 extra columns for our future use. all these are using nvarchar(50) datatype.
almost 95% of the columns in these are not in use almost. those are having null value and in some columns, the values are like 0,1...
is this affects the performance?
please spare your valuble time
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
Hello,
I have a table which resides on a diff.filegroup.
The space allocated is 7700MB.
Now ehen I use sp_spaceused 'table_name' to see how much space is left it's giving me data as
rows reserved data index Unused
at 1000hrs 629879 6777904 5068344 8 1709552
After an hour 637537 6780336 5070800 8 1709528
After 2hours 643883 6782560 5072904 0 1709656
After 2.5hrs 646887 6783584 5073920 0 KB 1709664 kb
after 3hrs 647239 6783712 5074056 0 KB 1709656 K
If you note rows are increasing,so is the reserved place and data but unused is also increasing.I think it should come down.
What behavior is this?How can I find out how much space is left in this table residing on diff.file group?
TIA
I would like to verify that we do not have any unused login ids. I know I can turn on auditing for successful login attempta and either manually check the logs or write some type of findstring code to capture successful login attempts and then for each success, remove the id from a master list, but I was wondering if there may be a simpler approach. Any thoughts?
Thanks, Dave
I have a 3rd party developed app which has over 42000 data fields in 1200 tables. It is a heavy transaction type app. Of these fields over 10,000 are null for every record. SOme of their homegrown references these fields (and this can be removed), and in many instances many of the fields are not referenced at all. My opinion is that these extra fields are adding a heavy overhead to updates, inserts etc to the server. Any disagreements?
View 2 Replies View RelatedI wrote simple script to check space used by tables:
CREATE TABLE #SpaceUsed(
TableName NVARCHAR(128),
NoOfRows INT,
Reserved NVARCHAR(18),
Data NVARCHAR(18),
Index_Size NVARCHAR(18),
Unused NVARCHAR(18)
)
GO
sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?'"
SELECT * FROM #SpaceUsed
SELECT
CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved,
CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData,
CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size,
CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused
FROM #SpaceUsed
DROP TABLE #SpaceUsed
and one of results looks strange to me:
TableName NoOfRows Reserved Data Index_Size Unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
T_TableXX 50081 38024 KB 37432 KB 640 KB -48 KB
Anyone know reason of such result (negative value of unused space)?
I was just reading a doc on SQL , that said that to free up the unused space from the DB , you should use the shrinkdatabase DBCC commnad. ....
I want to knw how to determine how much free unused space is there in a DB
Thanks,
What I am trying to do is get all of the records in a table that are out ofsequence so I know which account numbers I can reuse. I have a rangeof account numbers from 50100 to 70100. I need to know which accountnumbers are not stored in the table (not currently used) so I can use.For instance say I have the following data in table:Account Name--------- --------50100 Test150105 Test250106 Test4......I should see the results:50101501025010350104because 50101-50104 are available account numbers since not currently intable.Thanks.
View 2 Replies View RelatedOn a production database, there is a 2GB database, when I runsp_spaceused it indicates a very high quanity of unused space. Thedatabase has been shrunk & free space sent to the OS. Why is thisvalue so high, what can I do to reclaim the space?database_name database_size unallocated space------------------------------------------------------------------------------DB_00001 2004.13 MB 49.64 MBreserved data index_size unused------------------ ------------------ --------------------------------1531248 KB 412720 KB 165168 KB 953360 KB
View 5 Replies View RelatedI have inherited support for a database with many used tables. Thereisn't any documentation on what is used or dead. I was hoping to runtraces and capture the objectid but the property doesn't work thatway.Any good ideas would be appreciated.
View 3 Replies View RelatedI'm working on an app that contains a series of 100 very compute-intensive queries that altogether take 30 minutes on my laptop machine. The app may be atypical in that there is just this series of expensive queries, and not lots of simultaneous simple queries.
To speed up testing, I bought a Dell PE2900 Quad-Core Xeon with 16GB of RAM and four 15000rpm hard drives. It runs 64-bit Windows 2003 server and 64-bit SQL Server 2005 dev edition.
Unfortunately performance is worse than on my laptop. Task manager reports (almost constant) 13GB of available physical memory, while processor usage hovers around 30%. The page file usage is constant at 3 GB.
Where do I start trouble-shooting this problem?
Is there a way to see the the last date a report was accessed? I'm wanting to clean up our report server and get rid of old or unused reports.
View 1 Replies View RelatedHi,
I am unsure if i am managing my connections in the correct way... but as it stands we have them in a .dtsConfig file that each package references in order to get the relevant details..
This is working fine, except that in total our ETL project uses 6 different connections, and none of the packages utilise all 6.
This leads to an error message upon opening up EVERY package stating that there are connection strings in the .dtsConfig that are unused, yeah, thats the idea!
The way i have gotten around this, is to just include all 6 connection managers in every package, but my question is, is there a better way to achieve this same result??
are we going about this in a bit of a cackhanded way?
any incite would be useful..
Best Regards
Chris
I am working with a large database that has its tables stored on a secondary filegroup. I'm trying to shrink the size of the files but I can't seem to get the system to free up the unused space. I've tried shrinkdatabase and shrinkfile both with and without the truncateonly option. Has anyone else had this problem? Is there a workaround? Any help would be greatly appreciated.
Thank you.
Cathy
I have an old DATABASE that is not more in use , so I want to drop that database.
But before that I want to check that is there any application running on it or not.
can any one suggest me how to do this?
thanks
There are too many indexes built on DB. As per the naming convention it seems the indexes are built as per the suggestions provided from execution plan. I presume most of the indexes are used only once in a month for the reports but are hampering the performance of daily running queries. These are also occupying a lot of space.
To confirm on this I have used the below query to know & identify the unused indexes. I have recorded the counters before and after the huge operations and I observed NO CHANGE in any of the values.
What the below values exactly indicate and when do they change? Is it good to delete the indexes having low USER_SEEKS, USER_SCANS, USER_LOOKUPS?
Query:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
[Code] .....
I need to setup a Server Audit in sql 2012 standard edition. The goal is to identify all procs, views, tables, functions that are not being used anymore. What is the best way to setup a server audit to accomplish this? What events need to be captured at a minimum so I can delete all the unused crap in this database. This is a smaller, growing company.
View 2 Replies View RelatedI have a table. In the table I have different types of mailingaddresses. The addresses are linked by and ID. ex,addresses per ID 1ID address1 add21 add31 add4distinct Addresses in tableID address1 add21 add31 add4What I am trying to figure out is if this is possible. Is it possibleto figure out some how with a query that in the above example ID 1is missing add1. I am trying to populate a dropdown list in my codewith just the addresses that are left from the distinct list. I trieddoing an intersect something like this but that did not seem to work.SELECT typeFROM address_demoINTERSECTSELECT typeFROM address_demo AS address_demo_1WHERE progid = '12954892'I am wondering if I am moving in the right direction or if this is evenpossible......Thanks
View 1 Replies View RelatedI have a large table that I recently purged a year of data from.However, the table size in sp_spaceused hasn't decreased as much as Iwould expect. (there are no text or large object columns on thistable, BTW) Running sp_spaceused on the table shows the following.rows reserved data index size unusedTABLE_NAME23470880 67790808 KB 18116312 KB 3211616 KB 46462880 KBI ran a dbcc indexdefrag on all indexes on the table last night,including the clustered index, and I'm still seeing pretty much thesame amount of space reported as unused.The one thing that I haven't done yet is to run the sp_spaceused onthis table with the @updateusage flag set to true as I need to do thisafterhours to reduce contention. I will attempt this tonight andreport on the results.Here's DBCC SHOWCONTIG OUTPUT for the table:DBCC SHOWCONTIG scanning 'TABLE_NAME' table...Table: 'TABLE_NAME' (917578307); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 2264447- Extents Scanned..............................: 285484- Extent Switches..............................: 287092- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 98.59%[283056:287093]- Logical Scan Fragmentation ..................: 0.04%- Extent Scan Fragmentation ...................: 3.19%- Avg. Bytes Free per Page.....................: 2418.9- Avg. Page Density (full).....................: 70.12%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Looking through the history of the group, the one thing that seems toremedy this problem consistantly is to bcp out all the data, trunc thetable and bcp all the data back in. This is not really a possibilityfor me due to the size of the table and the availability expectationsof my customers. Is there anything that I forgot to check? Do youthink that it's just a matter of incorrect statistics in sp_spaceused?Thanks in advance:Matt
View 4 Replies View RelatedI'm trying to figure out how to reclaim unused space in a huge table (tbl) after setting one of the text columns to be the empty string. Other tables have foreign key columns associated with tbl.
After doing something like this,
update tbl set col = ''
the table doesn't automatically reclaim that space. If I do a direct insert into a new table like this,
insert into tbl2 (cola, colb, colc) select * from tbl
the new table is smaller as expected (about half the size in my case). Any ideas?
Rob
I apologise if this question has been asked before but I have spent ages searching these forums and the internet for an answer and I am yet to find one.
My problem is that I have a package which imports a column, lets call it 'Column A'. Column A is used to create other columns, lets say Columns B, C & D. This is done in a script using asychronous input and once completed column A is no longer used. Other tranformations occur to B, C & D including a split and then finally a merge together again, but all the time A seems to remain a valid input on all processes yet I never choose to use or output it. When I come to the merge process I am required to merge columns B, C & D but also A, surely this is inefficient. Furthermore when the package has run I get a warning telling me that Column A is not required and should be removed, but I can not seem to find anywhere to remove it from the pipe.
I am hoping that I am just missing something obvious here but I have been tearing my hair out so any help would be much appreciated!
Graham.
Hi,
I would like to get a list of all the unused columns, I have tons of files and fields in the package probably in the hundreds and I need to know which fields I am not using to see which fields are important not to miss out, so is there any way I can get them in a text file or any other format?
Thanks
I have a table contain ProductMaster and it is using in multiple table like sales,purchase,inventory,production and many of other tables. i want delete all product which are not using in any child table.
View 8 Replies View RelatedHello every one i had a design question.What would be the best way to approach a problem such as a perventing unsed and left open sql connections i have written a class called DataBase Manger but my question is should this class be static or none-static. right now the way i have is something like this private SqlConnection Get() { SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConnetionName"].ConnectionString); sqlCon.Open(); return sqlCon; } /// <summary> /// Closes a connection to the Database /// </summary> /// <param name="sqlCon"> Database connection to close</param> private void Let(SqlConnection sqlCon) { sqlCon.Close(); sqlCon = null; } then for example to run a store proc i have public int RunProc(string procName, params SqlParameter[] p) {
SqlConnection con = Get();
try {
using (SqlCommand cmd = CreateCommand(con, procName, p)) {
cmd.ExecuteNonQuery();
return (int)cmd.Parameters["ReturnValue"].Value;
}
} finally { Let(con); }
} now every time in any class or method i need to create a new Database manager and call the run proc object. Would it be a better desing to make this class static? so it would be just DataBaseManger.RunProc(blah,blah) or is it a better OO design to have it the way i have it. is is more secure in a webapplication? can these connections be hijacked? thanks in adanvce, any help would greatly be apperiated. or any link to any related tutorials thanks,Amir
Could some one help me to explain what does it means when I ran sp_spaceused on the database it reported ex. -15430kb for unused. Does this mean that the index ran out off space. Do I need to increase space for the database. I will try to run the dbcc check hope it will fix the problem.
Thanks.
Can anyone tell me a good way to monitor which indexes are not being used? Over time, I'm sure there are extraneous indexes in our database, which I would like to get rid of.
Any ideas would be appreciated.
Thanks,
Rob
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?
currently stuck with an issue where I need to reclaim the unused table space in SQL server.
ISSUE: Due to database size issue we have moved all the images from database to filesystem and want to reclaim the unused space now....I have tried shrinking the database and rebuild the indexes but didn't see any difference in the table or database size.
Similarly I have identified many Non clustered indexes on big transactional tables(~ 4 million records) that where not used since GO live 1 year back, so I wanted to drop these indexes to cut down the performance and maintenance overhead , so my question is will dropping these indexes reduces the database size?
best method to my issue regarding unused space. SQL SERVER 2014 BI EDITTION..I have a table that showed 62% Data, 7% unallocated, and 29% unused space.I ran the ALTER TABLE <table name> REBUILD, which changed it to 32% data and 67% unused.What I do with this table monthly and what I believe is causing this unused space issue is this:Every 1st of the month I get a csv file that holds around 3.2 million rows/15 columns of data. About 3 gbs every time.It's 13 months of data. I remove the last 12 months from the current table and import the new 13 month data.I do this twice a month and have no choice because the data is constantly updated and why we get it twice a month.So I am deleting around 6 gbs of data a month as well as adding around 6 gbs of data a month.
I believe this is why I end up with so much unused space, but just found the REBUILD command, which worked but now a small hit on performance and have too much space in the current table. I have read several times that shrinking the file is no good, but what other way to get rid of the extra space in this table? Also, the table doesn't have any indexes or primary key because of duplicates.
How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.
View 2 Replies View RelatedI am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
[code]....
I've recently inhereted an environment in which we have over 600 storedprocedures. The documentation is either very poor or non-existant andam needing advice on how to determine if a stored procedure has beenused. Does SQL Server have any sort of ticker that indicates when astored procedure was last used?Thanks and any additional information or experience would be greatlyappreciated.
View 2 Replies View Related