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
Hi, AllI use SQL Server 2000 and Win 2003 Server. I try to create a SQL ServerLogin audit log using Profiler, but cannot find the tools. I looked at theWindows Event Viewer, it only logs Windows authentications users for SQLServer not SQL Server users (Mixed mode users).Please help on creating SQL Server Login log (username,password and time).ThanksKai
View 6 Replies View RelatedHi
Hi can audit login fail and computer name for standard sql login?
Thank you
Alex
Is there a way to audit the login on SQL Server 2000? I'm looking for away to get a report login/logout events with the name of the database.Thanks in advance.
View 3 Replies View RelatedHi everybody,
I found 100 entries with milisecond
difference in error log
"Login failed for user 'sa'"
does it look like attack ?
1. Can I restrict number of failed login ?
2. What events to trace in order to get
ip address for for station that trying to login as sa and fail?
Thank you
Alex
I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
View 6 Replies View RelatedHi,
Im getting this error when attempting to retrieve data from an sql database.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword")
Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn)
Line 15: objConn.Open()
Line 16: objRdr = objCmd.ExecuteReader()
Line 17: While objRdr.Read()
Source File: C:finalyearproject2sample.aspx Line: 15
Please Help!! Im a beginner to this, so if anyone knows the answer, take baby steps when explaining. Thanks
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
View 9 Replies View RelatedI am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
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 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 RelatedHI 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.
I 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 Related